question

kiiiiiii avatar image
kiiiiiii asked

Database performance issue

frens ,

please tell me how to find the db performance issue in Sqlserver2005. give me somw tips.

vki

performancedbamonitoringtuning
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Good luck with that one. I would -1 you except for the fact that your reputation can't go negative.
2 Likes 2 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
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.
0 Likes 0 ·
Scot Hauder avatar image Scot Hauder commented ·
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
0 Likes 0 ·
Jack Corbett avatar image
Jack Corbett answered

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 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
+1 - for a good set of suggestions given what you had to work with.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

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.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
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.
0 Likes 0 ·
Rob Farley avatar image Rob Farley commented ·
SSDs are remarkably stable these days, but sure - RAM disk is good too.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.