x

how to test that my database is slow

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.
more ▼

asked Aug 03, 2010 at 05:04 AM in Default

freshdba gravatar image

freshdba
37 2 2 2

I think we'll need a bit more detail than that. 'Application using a specific is slow' doesn't really mean an awful lot to me.
Aug 03, 2010 at 05:11 AM Matt Whitfield ♦♦
You also need to include the full spec of the server, CPU, Mem, the entire disk config (i.e. # of disks, Raid setup, # of drives). Does the application utilize SPs. A good start would be http://www.brentozar.com/sql/sql-server-performance-tuning/
Aug 03, 2010 at 05:35 AM sp_lock
Has anything changed in the environment lately?
Aug 03, 2010 at 06:17 AM Blackhawk-17
I am tempted to re-tag this question with 'most-useful-information-received-from-very-small-amount-of-question-detail'...
Aug 03, 2010 at 12:47 PM Matt Whitfield ♦♦

@Matt But if you run MeasureString on this new tag, you might find out that it is wider than that cute rotating ball-of-tags on home page can fit :)

But seriously, this has become a remarkably useful question due to the number of great answers and discussion. I have learned a great deal from them today.
Aug 03, 2010 at 02:13 PM Oleg
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

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][2] and identify the slowest running queries.

Stuff like that can get you through, but you really need the baseline to do this correctly.

[2]: http://www.simple-talk.com/sql/database-administration/sql-server-tracing-an-automated-and-centralized-solution/
more ▼

answered Aug 03, 2010 at 05:15 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.5k 19 21 74

+1 - and welcome back (from holiday? - if so, hope you had a good one)
Aug 03, 2010 at 05:21 AM Matt Whitfield ♦♦
+1 for even starting to answer the question...
Aug 03, 2010 at 05:32 AM sp_lock
Thanks. Yeah, I was on "holiday." I spent a week at Scout Camp helping out 25 of the kids from our local Troop. I also taught the Nuclear Science merit badge to 6 poor kids who were foolish enough to sign up for it.
Aug 03, 2010 at 05:48 AM Grant Fritchey ♦♦
+1 - for teaching Nuclear Science to Scouts :) Things have changed since I went, the best I got to do was sharpen things and set fire to other things. Sometimes we sharpened things and then set fire to them and then threw them at each other, but generally we got stopped before we got that far.
Aug 03, 2010 at 06:14 AM Fatherjack ♦♦
Sounds like fun grant. Incidentally, if you are setting fire to things mixing a small amount of boric acid with methanol makes a brilliant green fire. I did it with my young kids last weekend and they were delighted. http://chemistry.about.com/od/funfireprojects/a/greenfire.htm
Aug 03, 2010 at 10:12 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Aug 03, 2010 at 05:49 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Aug 03, 2010 at 10:32 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

Excellent answer except for the hint. I really hate to suggest those to anyone just getting started in SQL Server tuning. It's far to easy to see them as a panacea and apply them everywhere. Still a +1 though.
Aug 03, 2010 at 10:37 AM Grant Fritchey ♦♦
@Grant, I see where you are coming from. To reemphasize, nolock should be used with great caution and forethought. But it has been truly useful to me in a couple of exceptional cases with tables that had long lasting locks where I knew ahead of time that a dirty read could be tolerated. If you have any doubt whatsoever about whether or not your case can tolerate a dirty read, do not use it.
Aug 03, 2010 at 11:35 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

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][2] white paper from Microsoft.

[2]: http://technet.microsoft.com/en-us/library/cc966540.aspx
more ▼

answered Aug 03, 2010 at 07:17 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left

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][1]

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.

[1]: http://www.sqlservercurry.com/2010/07/find-most-time-consuming-code-in-your.html
more ▼

answered Aug 03, 2010 at 11:08 AM

Mark gravatar image

Mark
2.6k 23 25 27

Yep. That will work... as long as the problem code is still in cache. If it has aged out of cache, then running queries against the DMV's won't get what you need. That's why I suggested gathering data with a server-side trace. It's much more likely to be sure to cover the bases. But that one can work.
Aug 03, 2010 at 11:15 AM Grant Fritchey ♦♦
Thanks for the clarification Grant. I suppose a SP could be written and put in a script that runs periodically and keeps the "most interesting" records.
Aug 03, 2010 at 12:04 PM Mark
Yeah, that's possible. I haven't tried it, but no reason it wouldn't work.
Aug 03, 2010 at 12:32 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x51

asked: Aug 03, 2010 at 05:04 AM

Seen: 4986 times

Last Updated: Aug 03, 2010 at 05:04 AM