Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Rovastar
Starting Member
38 Posts |
 Posted - 2005-10-28 : 11:51:34
|
| I wanted to have useful troubleshooting for SQL Server for a me and my fellow team members in a web hosting infrastructure environment for a large corp.None of us are SQL DBA gurus (including me) but we generally know enough to get around. We have multiple websites connecting to shared database servers.Anyway I wanted to write some documentation for basic how to start troubleshooting a databases in our environment so I thought I would share this with you and try and get more advice on how to improve it.First thing I did was set up a trace.I used a modified version of a standard traceBasically logging all the query activity that lasts over 1 millisecond of CPU time. Logging all go me too many results and that was not needed.http://www.milkdrop.co.uk/SQL/CPU_Usage.tdfThis trace is exported into a new database I call mine TraceResults. The idea is that I can have multiple traces tables within this thus used a whole new database.Within this table if you run a simple query you can find the queries that take the longest to run.Just looking at the longest running queries was no enough. We had a problem where in fact it was lots of traffic to a website all of it small queries but there were lots of them so I wrote a query to take this into consideration.None of this is record science. SQL queries or anything and I expect there is better stuff out there that is more useful.SELECT Sum([Tracetable].CPU) AS "Total CPU Time in MilliSeconds", master.dbo.sysdatabases.name AS "Database Name"FROM [Tracetable] INNER JOIN master.dbo.sysdatabases ON [Tracetable].DatabaseID = master.dbo.sysdatabases.dbidGROUP BY master.dbo.sysdatabases.nameORDER BY Sum([Tracetable].CPU) DESC; I ran this on the TraceResults table in Query Analyser and it gives an indication of the busiest databases.See the pics of the results.Http://www.milkdrop.co.uk/SQL/query.jpghttp://www.milkdrop.co.uk/SQL/Find_Problem_Databases.sqlhttp://www.milkdrop.co.uk/SQL/results.jpg(note: in the example the table name is Trace30-10-2005 or something you will have to change this to whatever your table is called)Now I’ll try and improve on this (it is in basic stages atm) and add some proper official looking documentation so DBAs that are not as experienced can find it more helpful. So I’ll publish my findings if anyone if interested.Is there anything obvious that I can add from more experienced minds here? Or that newbies might find useful. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-29 : 12:11:38
|
| It looks like you're using your brain, which is a good thing. Have you checked out www.sql-server-performance.com? They have tons of good articles on performance tuning that should help you a lot. Also, check out these articles:http://www.sqlteam.com/filtertopics.asp?TopicID=103MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-30 : 02:31:03
|
| Not quite what you were asking for, but in case it gives you some ideas:We deny new sessions on our web sites when the CPU activity on the SQL box goes over some set threshold. That takes care of the small queries problem, allows the existing sessions to "finish up", and get the thing going again. (That action is logged, so we can then look at what was going on at the time, and improve performance where needed).Kristen |
|
|
|
Rovastar
Starting Member
38 Posts |
Posted - 2005-10-31 : 11:23:11
|
| Thanks for teh feedback folks.I am looking through the links given. Although I actualy want to do something myself maybe witha slightly different slant to it. And it has to be something a skilled IT professional who is not 100% up on SQL Server can use. Can usThanks for the feedback folks.I am looking through the links given Baseline tips seems especially useful for me. http://www.sql-server-performance.com/gv_baselining_tips.asp. Although I am finding it difficult to find out really useful information, especially for diagnostics.I actually want to do something myself maybe with a slightly different slant to it. And it has to be something a skilled IT professional who is not 100% up on SQL Server can use. In a way I want them (and me) to follow a selection of simple procedures to get the correct results.Part of what I am trying to do is make traces and profiler more understandable to me. Having not used it much I get frustrated at the lack of information there. Why no database name? The column is there for selection and by returns nothing. Things like when using the tool do not promote confidence that all is working correctly for more novice like person to SQL Server.I want to keep the information controlled too. Not giving too much information and confusing people with stuff that is not needed.I did however add a couple more columns for Reads and Writes as these may be useful.However I do require some specific advice on this.By having SQL:Batch Completed and RPC:Batch Completed all I need to monitor activity on the database without any duplicates?What about for example SP:Completed is that needed? I am sure SQL:BatchCompleted covers this but as I cannot use the Kristen I will have to think about what you said but I would actually like the web requests to come first over the daily DTS junk that is being run. The web requests are the most important for us in the web hosting infrastructure for live production websites. The DTS that run for hours, doing heaven know what, can wait until a period when the web activity is quieter.More later thanks for teh help thus far. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-31 : 14:03:49
|
"DTS junk" |
|
|
|
|
|
|
|
|
Procedures for diagnosing problem servers
