October 12, 2007 at 3:35 am
Hi All,
I'm new to SQLSC and always willing to learn. I have my own way of tuning SQL server but I'm sure there is better ways, out there, of doing it. I have a 1TB DB, started a BBT (Black Box Trace) that will probably run for about 48 hours. Now this is where you come in.
How will you (step by step) identify & resolve issues derived from the trace. At this point I'm not making any headway therefore any assistance will be highly appreciated!
Regards,
S
October 12, 2007 at 6:09 am
Correct me if I'm wrong, but doesn't the black box trace only keep a rolling set of data? You'll need to set up an actual trace. Make it to file & roll the files over every... 100mb or so.
After that, load the trace files into a seperate database then clean the text output of the trace file so that instead of the full execution with all the parameters, you can simply see the procedure calls. From there, aggregate queries on average run time, average cpu, average i/o, max all three of the above, total from all the above. Take the top 10 from each of these lists and start identifying why the procedures are on the lists (poor indexing, poor sql code, etc.). From there, start solving the problems.
That's my general approach. Details can, and do, vary.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 12, 2007 at 6:56 am
the primciple of tuning is to identify the bottleneck/problem then apply. If you don't have a performance problem then tuning is not required, on the basis of "if it ain't broke don't fix it".
A profiler trace is fine other than, on a really busy server you won't be able to generate a full trace, I've tried and after 10 secs it dies. then you have to know what you're looking for, for instance profiler won't really help you with hardware problems.
I blogged briefly on this http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/08/10/what-s-in-a-counter.aspx
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 14, 2007 at 11:58 pm
Appreciate the feedback guys! I do agree with both point of views. Thing is there is always a specific set of actions to be taken. I started this thread to get out of the box ideas. You know that stuff which is almost magic, but do not get tought...
I thank you for your input and hope you have a fabulous day.
Regards,
S
October 15, 2007 at 5:55 am
Actually, I can add one more bit of info:
http://www.simple-talk.com/sql/performance/sql-server-performance-crib-sheet/
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 17, 2007 at 2:47 am
Thank You Grant,
Very insightfull...
Regards,
S
October 18, 2007 at 1:06 pm
Tracing should be used to identify specific reasons for specific problems. It comes later in the tuning process. You first need to answer the questions "Is there a performance problem?" (is the SQL Server actually performing fine), and if so, "Where is the problem?"
The key is monitoring. SQL Server keeps track of all sorts of useful information for you to use to determine what problems may exist. Go download the Waits and Queues document from MS:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx
** If you are reading this thread, and you haven't read that document, then go get it :D**
Print it out and read the first 10 pages. Lots of useful info there. That should change the way you think, and encourage you to keep reading - because the next 90 are good too (Much of that is a reference and doesn't actually need to be read until much later, when you have specific things to look up).
Whenever SQL Server has to wait on a resource - disk, memory, a returning thread, and so on - it will track how long it waited, and how many times it waited. Familiarize yourself with the sys.dm_os_waits_stats and sys.dm_io_virtual_file_stats. Set up a job and capture the output of them every 30 minutes into a table in a separate monitoring database. You can analyze the results and look up the specific wait states in the doc. It will tell you the next steps. The more you understand waits, the more you understand tuning.
You'll have an idea of what waits you encounter during specific times of the day, and then you can run profiler looking for specific things. For example, if you have too many SOS_SCHEDULER_YIELD waits, then you have a CPU problem (go find heavy-CPU queries, table scans, bad stats, and query recompilations). SLEEP_BPOOL_FLUSH, WRITELOG, etc. means you have I/O problems (look at disk/file config, look for queries using lots of physical I/O, use the data you captured from sys.dm_io_virtual_file_stats to ensure you're getting decent disk performance, etc.) ...and so on.
A large part of the Waits and Queues doc is a lookup for the specific wait types you'll encounter, many include the next steps to take (trace for specific queries, check specific counters in PerfMon, etc.).
Your mantra, whenever anyone complains about performance, should be "What are the waits?". Your monitoring, established above, can answer that question, and set you up to answer the rest.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply