hi team we use sql server 2005 64 bit, we have 25 databases in the system out of these i got a mail that. application using a specific is slow. how to test and resolve this issue. please help me, the size of specific database is 7 GB, some 120 users are connected to the server.
asked Aug 03, 2010 at 05:04 AM in Default
In order to determine if a particular database is "slow" you need to first determine a baseline. Establish what it looks like when it's not running slow and then you can compare.
Without that, the best you can do is start with a troubleshooting approach. Capture wait states and queues to determine what is causing the system to slow down. Capture query executions using a server side trace and identify the slowest running queries.
Stuff like that can get you through, but you really need the baseline to do this correctly.
answered Aug 03, 2010 at 05:15 AM
Grant Fritchey ♦♦
I will summarize.. Bottlenecks on CPU,Memory,and Disk are the main reasons for poor performance.
First step should be check for bottlenecks on the server, then you need to drill down why there is a bottleneck on these hardware. Basically all there hardware resources are interconnected, i.e if there is a bottleneck on memory then it will result in high CPU Usage and Disk usage(because of paging)
Once you track down the resource that is causing bottlenecks, the next step should be which processes is using these resources heavily, it can be from OS Side or SQL Server Side(Poorly written queries, wait statistics etc).
Once you find the high consumers the next step is to fix them, i.e you may need to create new index or drop the existing, partitioning etc
Also note if there is any incorrect configuration on the SQL Server like server wide or database settings (Auto Shrin) Or in OS Level Settings like paging or priority, can also cause performance issue
AS @GrantFritchey said, first you need to establish your baseline, once you established your baseline you can use windows performance monitor counters like memory,io,cpu,SQL Server counters(Locks,Memory Manager, Access methods, general statistics etc)Buffer to get the real time values, then you need to compare that with your baseline.
SQL Server also Provide verity tools and DMVs to track these performance issue like Profiler,Data Collector (SQL Server 08).
Remember the main cause of performance issues are Poorly written queries, Improper Database design, Lock Waits etc.
keep an eye on those
First, let me commend Grant and Cyborg for their excellent answers. (+1 each) But perhaps I can add a little that will be useful.
I agree with their calls to get a baseline. This will help you determine if it is slower than usual. Of course, whether an application is slow or not really boils down to this: Are the users happy with its performance?
Now, in general, the first place I look for ways to speed up a program is the code. In SQL, look for procedural code that can be turned into set based code. Similarly, look for row-by-agonizing row calculations. Jeff Moden has a whole series of articles on identifying and removing RBAR on SQLServerCentral.
Next, I would look at the indexes. I have seen cases where a developer did not add any indexes at all, and adding in a couple that made since more than tripled the execution speed (by the time I was called in to look at it, the database it was operating over had grown very large).
Then, look at locking. If part of your problem is that other processes are putting locks on tables you need, you may want to consider using the NoLock query hint. This hint should be used with extreme caution as it permits the possibility of dirty reads, but I have dealt with situations where a dirty read was acceptable but delays were not. Used carefully on a system with locking problems NoLock can bring enormous performance boosts. Of course, you can also look at the locking code and look for ways to minimize the amount it is locking and time it is holding those locks.
Finally, there is always the option of upgrading the hardware. As Cyborg said, knowing where the hardware bottleneck is will help you figure out what to upgrade, but as a rule of thumb in SQL Server, look at the RAM first. When in doubt, add more RAM. It is cheap, easy to add, and it will not make things worse.
answered Aug 03, 2010 at 10:32 AM
All above advices are great. In addition, I suggest you start from this blogs from brent ozar about tuning performance. Its a nice list of blogs and videos for a starter. Also check out Troubleshooting Performance Problems in SQL Server 2005 white paper from Microsoft.
answered Aug 03, 2010 at 07:17 AM
I'm sure all of these other people know more about optimization than I do (because I'm mainly a developer who causes the problems!) (+1 to all of you who answered)
But I received an email from MS recently that had a link to a page titled Find the Most Time Consuming Code in your SQL Server Database
So you might check out that link to cut to the chase so to speak. The tiny article shows short queries for both "Top 10 codes that takes maximum time" and "Top 10 codes that takes maximum physical_reads." It seems to me that those two would be good places to start.