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 |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
 Posted - 2008-07-07 : 06:46:21
|
| Hi all,Could any of you give me some pointers for measuring database performance levels in a production environment?I know this is rather vague so here are some specifics.The company I work for is in that painful transition from a small company to a medium one. We have a product that was built for small datasets and now that we are getting bigger clients with more data, a lot of our code is not scaling well. We have a lot of database logic that was coded by people used to iterative ways of doing things so the quality of the code in database logic is 'mixed'A typical set up is...We have a java application running on an application server that is connecting to multiple databases on a 2005 server. (1 database for each of our client companies typically) This provides a web front end with real time reporting etc etc.Some clients have their own application server and database hosted on their own dedicated server.Currently much of our business logic is done by stored procedures and functions in the database(s) and I've been asked to try and isolate performance problems.I'm currently wondering which metrics I should be looking for. I'm thinking that we could do with knowing...1) Index utilisation. Which processes correctly use an index and which result in lengthy table scans etc. I'd like to rewrite problem procs if we get the time or come up with better indices if we don't2) Raw utilisation. What logic gets used the most (so something like looking at the server after a day / week / whatever and seeing how much time the server has spent actually doing work in a particular object)3) Locking. Which tables get locked by which processes and why. Do they need to be locked - can we get away with dirty data?I've got a good deal of experience in sql scripting/development but I'm not a DBA (we don't have one) so I don't know what tools I've got available for this. Can anyone provide me with links to good resources and kindly contribute your experience to tell me what I should be looking for.We use SQL server 2005. I think enterprise edition.Thanks in advance,-------------Charlie |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-07-07 : 11:19:11
|
| "SQL" cursor code should be re-examined....and removed. Like shaving with a tweezers. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-07 : 11:23:57
|
| You can acheive these with SQL profiler . Use funnel technique to find out what needs to be improved. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-07 : 11:40:55
|
| Hi Thanks for the replies. The cursors I can handle -- they are easy to find as I have all the sources. Easy to find isn't necessarily easy to remove of course but I can deal with that.What is "the funnel technique" I've played around with SQL server profiler today on an empty dev server to get a feel for it but I'm still at the crawling stage, not toddling yet.Any good sites / guides / books you can recommend?-------------Charlie |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-07 : 11:54:00
|
| You can use some of these. http://support.microsoft.com/servicedesks/webcasts/wc011502/WC011502.ppt#312,25,SQL%202000%20Database%20Views%20of%20Performance%20JobYou can also find in:www.sql-server-performance.com |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-07 : 11:59:43
|
| Thanks again.I had a look at the presentation. I'll try and digest that tonight.-------------Charlie |
|
|
|
|
|
|
performance Indicators