Memory\*
MSSQL$ServerName:Access Methods\*
MSSQL$ServerName:Buffer Manager\*
MSSQL$ServerName:Buffer Node(*)\*
MSSQL$ServerName:Buffer Partition(*)\*
MSSQL$ServerName:Databases(*)\*
MSSQL$ServerName:General Statistics\*
MSSQL$ServerName:Locks(*)\*
MSSQL$ServerName:Memory Manager\*
MSSQL$ServerName:Transactions\*
MSSQL$ServerName:User Settable(*)\*
MSSQL$ServerName:Wait Statistics(*)\*
Network Interface(*)\*
Objects\*
Paging File(*)\*
PhysicalDisk(*)\*
Process(*)\*
Processor(*)\*
Server\*
System\*
I will apply an artificial load to the system while logging this information on the database server. In addition I will be sending details over the Smart array controller, which happens to be :
Server Type - HP Proliant DL360 G3 Memory - 2 GB of RAMDuel core Processor - 1 Intel Xeon Processor @ 3.06 GHz
Smart Array Controller - Compaq Smart Array 5iHard Drives:Compaq BD1468A4C5 - 146.8 GB @ 10K RPMCompaq BD14688278 - 146.8 GB @ 10K RPM
There is one physical Array set up as a RAID-0+1 mirrored with the two 146.8 GB hard drivesThe logical drive setting off this array is a C: Drive of 20 GB and a D: Drive of 116.72 GB
In order to look into the possibility of using a Network storage device we will be sending details about our SAN. We had a long discussion covering SQL Hardware, Network storage and virtualization. Here are some brief notes.
The 64 bit version of SQL 2008 performs significantly faster than the 32 bit version. The 32 bit SQL2008 can only address 4GB RAM.
There is an industry trend towards SAN solutions. But special care has to be taken when configuring them. Mathias will also ask details about the NetApp application in Bermuda and Zurich. This will include questions like
- How many spindles (disks) are installed in the server ?
- What kind of disks ? 10k rpm or 15k rpm
- How many logical disk drives (LUNs) are there ? And for what the are ?
It is very important to configure virtual drives which match the IO characteristics of the data stored. In particular MDF and NDF files have random R/W characteristic which need sufficient spindle allocation. Where as LDF (transaction logs) require sequential write characteristics. I described extreme performance drop that I experienced in October particularly concerning the TempDB. Apparently this is characteristic of a bottle neck needing lots of R/W operations. When configuring the NetApp shares it makes a lot of sense to use 64kbit block sizes. The reason is that SQL uses pages containing 8 blocks of 64 kit. If the same size is used on the NetApp share then page swapping becomes faster.
We had a discussion about virtualization of databases using VMWare.. VMWare servers boot from drives stored on VMFS shares. When virtualizing an SQl server it is important to use a RAW drive because these are expandable where as VMFS drives are of fixed size. The bottle neck of virtualized servers are the HPA ports because these can limit the i/o throughput. The main advantage of virtualization is that less hardware is needed. Cloning servers has been available with windows for a number of years, therefore cloning is not a good reason for virtualization. Mathias offers a service to analyse the work load of existing servers using a capacity planning tool.
