March 1, 2011 at 2:33 pm
Indianrock (3/1/2011)
CirquedeSQLeil (3/1/2011)
Indianrock (3/1/2011)
Actually now I'm hearing that MPIO is not on. Systems is still looking into all of that. Each of the data and log files shown below is on it's own lun. The "split_data" paths are mount points in the K:\data folder. Tempdb has something similar. Are you saying there is no advantage to this arrangement?K:\data\OurDBName.mdf
L:\LOG\OurDBName_LOG.LDF
K:\SPLIT_DATA\CM_Data1\CM_DataFile_01.ndf
K:\SPLIT_DATA\CM_Data2\CM_DataFile_02.ndf
K:\SPLIT_DATA\CM_Data3\CM_DataFile_03.ndf
K:\SPLIT_DATA\CM_Data4\CM_DataFile_04.ndf
K:\SPLIT_DATA\CM_Index1\CM_IdxFile_04.ndf
K:\SPLIT_DATA\CM_Index2\CM_IdxFile_03.ndf
K:\SPLIT_DATA\CM_Index3\CM_IdxFile_02.ndf
K:\SPLIT_DATA\CM_Index4\CM_IdxFile_01.ndf
K:\SPLIT_DATA\CM_Data1\CM_LogFile_01.ndf
K:\SPLIT_DATA\CM_Data2\CM_LogFile_02.ndf
K:\SPLIT_DATA\CM_Data3\CM_LogFile_03.ndf
K:\SPLIT_DATA\CM_Data4\CM_LogFile_04.ndf
K:\SPLIT_DATA\CM_Index1\CM_LogIdx_04.ndf
K:\SPLIT_DATA\CM_Index2\CM_LogIdx_03.ndf
K:\SPLIT_DATA\CM_Index3\CM_LogIdx_02.ndf
K:\SPLIT_DATA\CM_Index4\CM_LogIdx_01.ndf
Is this representing two separate databases? Just one database
Have you run any benchmarking tools such as SQLIO or IOSIM? No, but we use Quest Spotlight and Quest Performance Analysis for SQL Server along with other tools such as the performance dashboard reports.
What is your allocation unit size (8k or 64k or something else)? Systems handles the Netapp
Is your partition properly offset? Can't answer that one.
How is the volume fragmentation? I've read this isn't pertinent on a SAN like a netapp.
Have you checked the growth sizes of your data and log files? We bumped them up to 1 or 2GB generally quite a while back. I couldn't convince anyone to turn on instant file initialization -- again, they thought it irrelevant to a SAN
What is the VLF fragmentation looking like? I shrunk/rebuilt the single log file quite a while back until it was pretty good. The file is now 135GB on it's own 350GB lun.
What is the RAID config of your netapp (RAID DP or something else)?
Raid 6 I'm told
I would look at reducing the number of datafiles you are using - it's probably too many (17 data and 1 log by my count).
I would also check with your Systems dept and see what Cluster size (allocation unit) they set. 64K is typical for databases.
I would also look at running SQLIO and IOSIM to verify your IO performance - they are free tools from MS.
Also, it looks like you have separated out Indexes from the data, what is your filegroup setup? Are indexes per table in the same filegroup as the CI/heap or did you completely separate NCs from the CI/heap?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 1, 2011 at 4:08 pm
[font="Tahoma"]I would look at reducing the number of datafiles you are using - it's probably too many (17 data and 1 log by my count). [/font]
[font="Arial"]I'm beginning to think you're right.[/font]
[font="Tahoma"]I would also check with your Systems dept and see what Cluster size (allocation unit) they set. 64K is typical for databases. .[/font] [font="Arial"]ok[/font]
[font="Tahoma"]I would also look at running SQLIO and IOSIM to verify your IO performance - they are free tools from MS. [/font] [font="Arial"]I've heard of sqlIO, may just do that.[/font]
[font="Tahoma"]Also, it looks like you have separated out Indexes from the data, what is your filegroup setup? Are indexes per table in the same filegroup as the CI/heap or did you completely separate NCs from the CI/heap? [/font] [font="Arial"] Yes, numerous file groups and we did separate out indexes and data. CI/heap ??[/font]
March 1, 2011 at 4:17 pm
Table data is either in the Clustered Index (CI) or in a heap thus I shortcutted it to CI/Heap.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply