frens ,
please tell me how to find the db performance issue in Sqlserver2005. give me somw tips.
vki
asked
Database performance issue
Good luck with that one. I would -1 you except for the fact that your reputation can't go negative.
Yup -1. I guess the answer to match the question would be. "Change the settings". Seriously though, if you can give us more detail on your problem we might be able to help.
I'm not sure what your issue is--but I know Microsoft's answer to your problem:
http://www.microsoft.com/sqlserver/2008/en/us/why-upgrade.aspx
That's a very broad and open-ended question so I'll provide a broad and open-ended answer.
Learn how to use Performance Monitor. Lookup Wait Stats online.
When building queries read the execution plans and try to eliminate scans, especially table scans.
Check for non-set based code.
+1 - for a good set of suggestions given what you had to work with.
There are multiple books on the topic. One of the new ones to come out that I'm finding very informative is "Professional SQL Server 2008 Internals and Troubleshooting." There are lots of others as well.
One easy answer to many performance issues is to throw more RAM at a machine, and this could also be a good stop-gap until you have found the real culprit.
Try running a trace for queries that have a very large number of reads (use a filter to do this).
Run Performance Monitor and see how what the Page Life Expectancy of the Buffer Cache is doing. It should be slowly increasing over time.
An overall understanding of the SQL system will help, so books like SQL Server 2008 Internals by Kalen Delaney will be useful.
But for a quick and dirty solution, throw more RAM at the machine for now, and keep looking.
Also... go and buy a Solid State Disk and put your tempdb files on it. It writes much more quickly, and this will almost certainly help performance too.
I like your suggestion about ssd disks, but how long Will they last in a heavy write intence dB like tempdb? Dou you have any experience with ssd? Have you tried a huge ram disk instead for the tempdb? I have heard that they outperforms ssd.
SSDs are remarkably stable these days, but sure - RAM disk is good too.
