Tuning a Database
-
Guys,
I was given 5 GB database to perform tuning on it because it is runingnvery slow and the respond rate is bad.
What is the best way to get started on this, since I am not familiar with the database and I don't know where to begin? Tuning expert please help.
-
You can't tune what you don't know. Also ask them what is slow, how do you know it is slow, and where is it slow at? After you found out the answer you probably know where to start from.
mom
-
You can also use the profiler to find out the slowest running queries and optimize them... and at the same time you could throw that trace in the index tuning wizard to see if you can't get a big boost from there.
-
At the min you should use System Monitor and Sql Profiler (there is a lot of info in BOL and this site).
Good Luck
-
Thank you.
-
I can strongly recommend Ideras SQL diagnostic manager
at £450 per server it's a bargain. this should then give you a report on the slowest running Queries and procedures along with min,max and average execution times along with CPU, disk and network activity logged over a period of time.
It's an excellent tuning tool and one we can't do without here.
MVDBA
-
Look for cursors, while loops, and views of views to start with. Also look for tables with no primary keys. The least expensive way to find this is to write a script that interogates the system tables... for example, here's one way to look for cursors...
--Find name of proc, view, function, etc containing text from "Like"
SELECT DISTINCT so.Name, so.Xtype
FROM SYSOBJECTS so,
SYSCOMMENTS sc
WHERE so.ID = sc.ID
AND sc.Text LIKE '%CURSOR%'
ORDER BY so.xtype,so.Name
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Let's jump back to about 30,000 feet before you jump into profiler, stored procedures, queries and rewriting things and the 'tuning' wizards ...
Do you do regular database backups ?
If your recovery mode is full are you doing transaction log backups frequently enough ?
Are you executing SQL maintenance Plans ?
More specifically 'Integrity' (dbcc checkdb and dbcc newalloc) ?
Are you 'optimizing' the database via a SQL maintenance plan (update statistics) ?
Have you checked the 'fragmentation' of tables and indexes (dbcc showcontig) ?
(I'd start from the largest table to the smallest. Tables less than 64kb reserved space ignore.)
On the hardware side ...
Is it a multiple CPU server ?
How fast are the CPUs ?
How much RAM is present ?
Are you using internakl disk, RAID arrays or a SAN ?
Other areas ...
How many users use the server ?
Is the server primarilty an online (OLTP) server, batch processing or a combinatino ?
How many users connect concurrently ?
I'd answer all of the above post-haste as the initial investigation before doing anything !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
All good points, Rudy... I'm used to good DBA's that take proper care of those things, so I tend to forget about them. My big problem has been that most of the Developers I have to do code reviews for seem to have no clue what set-based programming is all about... they normally brag when they can add 1200 new customers to the database in a whopping 3 hours
. Thanks for the reminders you provided.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Wow that's good... I didn't know anyone who could type that fast
. -
Awesome, ain't it?
Most good SQL Developers would be disappointed if that took more than a handfull of milli-seconds regardless of how many tables need to be updated for the new customers. The good thing is, no matter how bad a day may go, my code always looks great 
I don't use 'em, but someday, I'm going to have to see if I can write a cursor that will run that slow 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I bet you can't even with the waitfor
. -
Hi,
The Best way to trouble shoot General Slow Performance of SQL Server is to look for any obvious reason that you could point to like any changes since the last good performance, update stats, index defrag etc. (do not waste too much time on these if you are not sure).
Collect Trace for about 30-60 minutes (Use sp_trace_create, sp_trace_setevent, sp_trace_setstatus) for Events SQL Batch Starting / Completed, SQL Statement Starting / Completed, SP:Starting / Completed, SP:Stmt Starting / Completed, RPC:Starting / Completed, Performance:ShowPlan Statistics, Showplan all, SP:Recompile, SortWarnings, MissingColumnStatistics, Database:AutoGrow,AutoShrink.
Add Data Columns: Apart SPID, DatabaseID, TextData, EventClass, StartTime, EndTime, CPU, Duration, Reads,Writes, NestLevel, ClientHostName, ApplicationName
Download the Read80Trace tool from Microsoft site:
< http://support.microsoft.com/default.aspx?scid=kb;en-us;887057 >
Run this tool with your trace files, it takes about 5-10 minutes and give you the top 10 queries by CPU,Duration,Reads, Writes. Tune these queries and your database will be back on track. (Use the Showplan Statistics event of the trace to look at the execution plan and the state of statistics etc, look at Sort warnings for any Sorts spilling to TempDB due to memory shortages, if there are any Auto Grow, Auto Shrinks, Create Auto Stats, Missing Stats etc )
Also look for Blocking / Wait Stats. Down load this script from Microsoft site to monitor Blocking and waitstats:
http://support.microsoft.com/kb/271509/EN-US/
Also take a look at this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224453
Hope this helps,
M.S. Reddy
-
Thank you guys. I have started looking at those things.
-
Thanks M.S. Reddy ... good stuff ... one can always stand to learn about something 'new' every day !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply