| Author |
Topic |
|
TSQLMan
Posting Yak Master
160 Posts |
 Posted - 2006-10-04 : 17:19:54
|
| I am having performance issues with one of my mission ciricial SQL Servers. A Payroll, and Timekeeper Web Applications are using this SQL server as a back end. Users have been complaining about two weeks the SQL Server has been very slow. Each app has it's own web server, and both are performing slowly. Basic specs, and performance data: Dual 1Ghz Xeons2GB MemoryHigh End SCSI Drives, and Controllers RAID 5One Database is about 6GBAnother about 11Buffer Cache Hit Ratio is always around 95%60 to 80 trx per second avgAverage Processor Usage 26% unless data syncs are runningand then processor will Avg. about 48%The apps that are hitting the server are web apps using sp_executesql to run selects, updates, inserts, and deletes. (seem to impact the server processor and memory very little)Any suggestions as to what I may check to further diagnose the problem?Thanks, TSQLMan |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-04 : 18:01:07
|
| 1. Capture some of that SQL in SQL Profiler and run the Index Tuning Wizard on it.2. Increase memory to 3GB. |
 |
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2006-10-04 : 18:09:36
|
| I will try that, although I will be a little hesitant to let it make any changes. The databases were not developed in house, and up until the last two weeks, been very efficient. Altough the memory upgrade should have been done long ago. Thanks, |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-04 : 18:13:52
|
quote:
I will be a little hesitant to let it make any changes.
You don't have to make the changes, but at least you'll know if indexing is a problem - it may not be. If it is, then you can either add them yourself or go back to the developers and suggest the indexes. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-05 : 02:06:01
|
| 6GB and 11GB is pretty big for a time keeping database!Do you have maintenance procedures for REINDEX / INDEXDEFRAG and updating the statistics? Also recompile (although sounds like you don't have any Stored Procedures) - if you didn't recently reboot that server might be worth stop/start the SQL Service to kill the stale cache, and let it make a fresh one. (Date/time is at the top of the SQL Error Log - E.M. Server : Management : SQL Server Logs)Did anything change 2 weeks ago - at the time people started noticing it was slower?Is SQL on its own box? or is it sharing the box with IIS? (The first would be a better answer!!)Has anything been added/changed on the box recently?Any DNS changes which could have fouled up the routing (or searching!) of network packets?Is the SQL port open to the web (and possibly getting loads of twits trying to guess the SA password? If you are not sure turn on RECORD UNSUCCESSFUL LOGIN which will then show them in the SQL log - Enterprise Manager : Right click the Server : Properties : [Security] : Audit level : "Failure")Kristen |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-10-05 : 07:58:51
|
| Our friends over at sql-server-performance has some pretty good articles when it comes to performance tuning. http://www.sql-server-performance.com/performance_monitor_counters_sql_server.aspI'm no expert on performance tuning but indexing is in every case critical to an OLTP database. I'd probably look for locking/deadlocking issues and run Profiler to find any long-running queries and work on those first. Then maybe create some sort of table partitioning. I've had great performance gains in partitioning critical tables into "current" and "historic" tables...might be worth considering in addition to/instead of hardware upgrades.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-10-05 : 09:42:58
|
quote:
Originally posted by Kristen
6GB and 11GB is pretty big for a time keeping database!
Not if this is from a certain company based in Chelmsford, Massachusetts. They do not exactly give you a way of archiving/deleting any of the old records. We have punch records from the last 6 years.If this is the same company that I am thinking of, their helpdesk always whines about defragging indexes. I think their DBAs just tossed them that line to get people to shut up once in a while. Still, it may get you past their level one support.Are both databases are timekeeping applications? |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2006-10-05 : 10:51:02
|
quote:
Originally posted by Kristen
6GB and 11GB is pretty big for a time keeping database!Do you have maintenance procedures for REINDEX / INDEXDEFRAG and updating the statistics? Also recompile (although sounds like you don't have any Stored Procedures) - if you didn't recently reboot that server might be worth stop/start the SQL Service to kill the stale cache, and let it make a fresh one. (Date/time is at the top of the SQL Error Log - E.M. Server : Management : SQL Server Logs)Did anything change 2 weeks ago - at the time people started noticing it was slower?Is SQL on its own box? or is it sharing the box with IIS? (The first would be a better answer!!)Has anything been added/changed on the box recently?Any DNS changes which could have fouled up the routing (or searching!) of network packets?Is the SQL port open to the web (and possibly getting loads of twits trying to guess the SA password? If you are not sure turn on RECORD UNSUCCESSFUL LOGIN which will then show them in the SQL log - Enterprise Manager : Right click the Server : Properties : [Security] : Audit level : "Failure")Thanks, Kristen
SQL is on it's own Box. We have Maintenance Plans, and weekly a manual dbcc dbreindex is performed on all the tables, I recently did and sp_recompile on the database. We do have sprocs, but reports are the only thing, that uses them. As a matter of fact, we did have some routing changes, but I will have to research to see if they could have possibly affected that server. We have intrusion detection running, and I have done a profile on login attempts already. Thank you for the suggestions, I have something else to look at. I remember thinking it could be nework related, but that is not my area of expertiese. |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2006-10-05 : 10:55:54
|
quote:
[i]Are both databases are timekeeping applications?
No one is Timekeeper, and other other is HR/Payroll |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-05 : 11:20:55
|
| Have you verified that it is really SQL Server that is slow? Maybe something is wrong on the web servers.I would use profiler to see what the long running transactions are to verify that it is really SQL Server that is slow.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-05 : 13:50:18
|
| Bit late if you don't already have it! but it would be good to have some baseline tests - including some local to the SQL box itself - to enable easy testing of what part of the system is suffering slowdowns.Kristen |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2006-10-07 : 21:22:24
|
quote:
Originally posted by Kristen
Bit late if you don't already have it! but it would be good to have some baseline tests - including some local to the SQL box itself - to enable easy testing of what part of the system is suffering slowdowns.Kristen
Turned out to be an authentication issues on the SQL Server, after I deleted and recreated spn's on the SQL Server Service User it straightened right up. TSQLMan |
|
|
|
nitin1353
Constraint Violating Yak Guru
381 Posts |
Posted - 2006-10-10 : 01:29:40
|
| Hi I could not get this"Turned out to be an authentication issues on the SQL Server, after I deleted and recreated spn's on the SQL Server Service User it straightened right up."Can you please elaborate?RegardsNitin |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2006-10-10 : 17:42:58
|
| spn stands for Service Principal Name. Basically anythime a user is running a web application using Windows integrated Authentication, when the client requests data from the web server, the web server then passes on the users credentials to the SQL Server, which creates a double hop scenario, forcing Kerberos Authentication vs. NTLM. A Kerberos double hop scenario requires and spn.In our case web app users were continually requesting a piece of data on another SQL Server, which forced the SQL Server Service user to authenticate to another SQL Server, and that was failing, which was intermittently causing a delay in the return of data, therefore giving the appearance, that there was a performance issue with the SQL Server. |
|
|
|
|
SQL Server Performance