December 01, 2020, 08:37:08 pm
News: If someone gives you crap then give them a Bad Star
Pages: [1]   Go Down
Author Topic: Finding sql server preformance issue (missing a doc) **Resolved**  (Read 13407 times)
simmo
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8560



« on: July 27, 2006, 01:38:16 pm »

On my database server I've been monitoring the preformance logs because things are happening that I cannot explain.
I've noticed that my disk queue lenght will get to 3 sometimes and I've read that more than 2 is bad. The server has 3 physical disks, one labeled system, one labeled sql logs and one labeled sql data... I think that I need to have the last two changed to raid 5 arrays to speed things up (or should it just be a stripped array with no parity?)
Any thoughts?
« Last Edit: February 08, 2007, 09:12:53 pm by msimmons » Logged

                 ..... .....

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #1 on: July 27, 2006, 09:12:18 pm »

Depends how big the database is? Where is the database file (which logical partition within SQL Server) and where is log file (which logical partition within SQL Server).

Also if different logical partition within SQL Server are on different disks.
Logged

simmo
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8560



« Reply #2 on: July 28, 2006, 01:25:36 pm »

This server hosts a few pretty large databases (and one networked program that uses the largest one (which i think is a bad idea)) but as for your other questions, Im not sure I follow... how would I find those things out?
Logged

                 ..... .....

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #3 on: July 28, 2006, 10:39:20 pm »

When you create a new database u can see an option called ON PRIMARY (this is a SQL Server logical partition on ur HDD partition)

You can create a SECONDARY or LOG (on another physical HDD partition) and put the LOG file there so the IO bottleneck will be split for 2 different physical HDD.

This ofcourse is usefull for large databases with heaps of transactions going around.
Logged

simmo
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8560



« Reply #4 on: July 29, 2006, 12:58:04 am »

judging by the labels of the physical disks (i know its bad to assume) I believe they set up the log files and data on two seperate physical disks... but we're still getting the bottleneck described in the first post
Logged

                 ..... .....

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #5 on: July 29, 2006, 03:30:06 am »

That was one step towards the solution. There are heaps other steps.

See this document.



Hope this helps.
Logged

simmo
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8560



« Reply #6 on: July 31, 2006, 09:10:01 pm »

Thats a pretty extensive doc, and yeah, it helps thanks!
Rebooting helps too
Its looking better than it has been but this weekend I'm going to reboot it again and run a baseline performance log and then a few during the week and using the details in the document should be able to determine where to go from there.
I did find out that the server does have an array, it just doesnt tell windows about it.
« Last Edit: July 31, 2006, 09:10:22 pm by msimmons » Logged

                 ..... .....

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #7 on: August 01, 2006, 01:17:36 am »

Well thatz the document we used when one of our client was having problems with SQL Performance and that document helped us a lot finding some bottleneck issues.

It was RAM
Logged

simmo
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8560



« Reply #8 on: February 03, 2009, 03:34:09 pm »

Hey wrack, could you reattach that doc? It seems to have fallen off in one of the forum upgrades and I don't have a local copy.
Logged

                 ..... .....

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #9 on: February 03, 2009, 11:14:19 pm »

Ofcourse dude...I need to do it when I get back home though since it's on my personal laptop.
Logged

simmo
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8560



« Reply #10 on: February 04, 2009, 12:11:55 am »

awesome, thanks. I think I found what the problem I am having this time is... the raid array controller card battery died... have to wait till tonight to go to the datacenter so i can power down the server, open her up, and get the part number off the battery Another Tounge
Logged

                 ..... .....

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #11 on: February 04, 2009, 10:08:18 am »

Hey simmo, for some reasons I couldn't upload the doc here (452 KB bruce_h4h) so I have uploaded it to my website. Below is the link.

http://www.codelake.com/techpages/SQL_Server_Performance_Audit.pdf
Logged

simmo
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8560



« Reply #12 on: February 04, 2009, 05:00:57 pm »

Awesome, thanks! I can't upload it either, I thought as admin the rules wouldn't apply laugh1
« Last Edit: February 04, 2009, 05:02:43 pm by msimmons » Logged

                 ..... .....

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #13 on: February 04, 2009, 11:33:59 pm »

Most welcome mate beerchug
Logged

Pages: [1]   Go Up
Print
 
Jump to: