SQL Performance

rajdeepsamanta

We have 90GB database running in Native Database, we want to convert it into SQL server 2005 and we have also 41 user.

Can any one sugest us what is the minimum hardware configuration for running and convert the database in SQl 2005 smoothly.

stryk

Hi!

If you search for "SQL Performance" you will find plenty of advices and recommendations.
At "Directions EMEA 2008" Waldo presented a great session about HW recommendations, he provides the slides for download: http://users.telenet.be/waldo1001/TECH02.pptx

There is also the "NAV Hardware Guide" which gives some recommendations, which are IMHO insufficient, actually undersizing the systems.

Here my proposal:

Platform: x64
CPU: 2 QuadCore
RAM: absolute minimum 8GB, better 16GB - the more the better
Disks (this should be investigated/discussed thoroughly):
C:\ RAID1 (OS, Swap, SQL Server Progs.)
\ RAID1 (master, model, msdb)
E:\ RAID1 (tempdb)
F:\ RAID10 (NAV mdf/ndf)
G:\ RAID10 (NAV ldf)
H:\ RAID1 (Misc, local Backups)

Requires min. 16 spindles.

This is a somewhat optimal setup, capable to run a high transaction volume. The minimum requirements could be:

C:\ RAID1 (OS, Swap, SQL Server, master, model, msdb, tempdb)
\ RAID1 (NAV mdf/ndf)
E:\ RAID1 (NAV ldf)

Requires 6 spindles. This is what I call a "Mickey Mouse" configuration and is only feasible for small databases with low transaction volume.

According to the disks it is important which technology you use: SAS is better than uSCSI, SATA should not be an option at all. Maybe you're using as SAN with iSCSI or Fibre Channel, etc. - as mentioned: TO BE DISCUSSED!

Hope this helps you a little.
Regards,
Jörg

davmac1

NAV claims to make minimal use of tempdb. Is that not true in your experience?
Also, does the use of master, model, and msdb provide enough activity that it needs a separate spindle from the NAV database?

Do you have info on the relative peformance of the different disk technologies you outlined?

I had thought bumping up to 32GB RAM (max on Win64 standard) with 2 RAID 1 and 1 RAID10 (with 8 or 10 drives) would provide great performance for a high volume database.

How much difference in performance do you think we would see between the standard Dell 15K SCSI drives and the higher performance drives?

stryk

davmac1 wrote:

NAV claims to make minimal use of tempdb. Is that not true in your experience?

Well, the queries sent from NAV are not really challenging the SQL Server so that tempdb is moderately used - depending on the transaction volume.
But when running huge Indexing operations (rebuild, etc.) heavy load could be put on tempdb.
MS generally recommends to isolate the tempdb; which is not always mandatory.

davmac1 wrote:

Also, does the use of master, model, and msdb provide enough activity that it needs a separate spindle from the NAV database?

Depends on the number of processes and transactions. All kinds of processes have to be maintained in "master" - and the "lock requests", a problem which could be raised by "Always Rowlock".
"msdb" gets important if you're running lots of SSIS Packages, Jobs, etc..
"model" could be ignored.

davmac1 wrote:

Do you have info on the relative peformance of the different disk technologies you outlined?

Well, just few things: SAS throughput is 3.2GBit/sec, uSCSI is 320 MBit/sec, SATA has a way too high MTBF (Mean Time Between Failure).

davmac1 wrote:

I had thought bumping up to 32GB RAM (max on Win64 standard) with 2 RAID 1 and 1 RAID10 (with 8 or 10 drives) would provide great performance for a high volume database.

Sufficient RAM is crucial. Disks can be a problem anyway - again: it depends! This is an issue which MUST be discussed thoroughly according to the specific demands!

davmac1 wrote:

How much difference in performance do you think we would see between the standard Dell 15K SCSI drives and the higher performance drives?

See above "throughput". Also, some SAN solutions provide large NVRAM Buffers which also could speed up things tremendously. Again: It depends!

bbrown

We have a site with a similar sized database (120 GB and 50 users). They have a fairly high trasaction volume (>1,000,000 new Value Entries per month) and have limited downtime for operations such as Adjust Cost and DB maintenance.

The server host a live and training database. Training is a recent copy of live and so is a similar in size.

Platform: x64
CPU: 4 QuadCore
RAM: 32 GB
Disks: all drives are 15K
C:\ RAID1 (OS, SQL Server Progs.) (2x72GB SCSI)
\ RAID1 (master, model, msdb, NAV mdfs) (2x72GB SCSI)
E:\ RAID1 (paging file) (2x72GB SCSI)
F:\ RAID10 (NAV ldf (live)) (4x72GB SCSI)
G:\ RAID10 (NAV ldf (training)) (4x72GB SCSI)
H:\ RAID10 (NAV ndfs) (14x72GB SAS)
I:\ Single 500 GB USB External (scratch space - storage of fobs)
J:\ RAID10 (Maintenence disk) (14x72GB SCSI)
K: RAID10 (TempDB) (4x72GB SCSI)

davmac1

If the locks are all buffered in RAM, then how does a separate RAID1 help for the master database?
If SQL Server is writing locks to disk, wouldn't that kill performance?

stryk

davmac1 wrote:

If the locks are all buffered in RAM, then how does a separate RAID1 help for the master database?
If SQL Server is writing locks to disk, wouldn't that kill performance?

Hmmm ... good question, I'm not sure about this ... :-k
I just think that there is "pressure" on the master db, because all processes - and locks etc. - are mantained there ... so, if you're reading from e.g."sysprocesses" or "syslockinfo", you think it's all exclusively handled in RAM?

davmac1

I don't know. Logically, it seems like there would be problems if it had to write all the locks to disk. We have been encouraged to have enough RAM to hold all the locks in memory. Perfmon on well performing system should almost always show a very small disk queue.
This may be a question to refer to the SQL Server group,

BTW - I should be at PASS this year for the first time. It is in Seattle, so there should be a lot of Microsoft's top SQL Server people there.

garak

in my oppinion the locks are handled in RAM.

For manage the locks (SQL Server managed) there are the parameter "Locks" in Server Property -> Advanced (or so)

With this parameter, you set the total number of available lock entries that the lock manager can handle. In other words, this value represents the maximum number of rows allowed in the syslocks table. Be aware that each lock takes 32 bytes, and the syslocks table resides in memory after SQL Server is started. The memory block that manages locks is fully allocated as soon as SQL Server is started. For example, the default of 5,000 locks takes up 160,000 bytes (32 multiplied by 5,000) of the memory allocated to SQL Server.

How do you know if you need to make this value larger? An error message tells you that you have run out of locks. If you are running a very large transaction when this occurs, you may need to break the transaction into smaller pieces. Monitoring the total locks in Performance Monitor during medium and high loads is the best way to determine whether you are getting close to the limit. You will find the Total Locks counter in the SQL Server Locks section of the counters.

So also books online (or press F1 in Field "Locks")

stryk

Yes, you could fiddle with the "Lock" parameter, which I do not recommend - usually SQL Server's dynamic management is the best ...

But the question still remains: Even though locks are managed in RAM - actually ALL transactions are managed in RAM - is there a physical I/O (asynchronously, checkpoint triggered) on the "master" database?

I had cases where it looked like that, but haven't found any "prooved evidence" so far ...

kriki

I also think Locks remain in memory:
I base it on the following:
-32bit environment : the memory over 2GB is only for caching data and not for caching plans and locks
-Avoid using "Rowlock" because it uses a lot of memory. If you have 64bit and enough memory, you can do it.

bbrown

Locks are maintained in RAM. Also, with SQL 2005, much of the stuff used to be in the Master database is now in the Resource database. This may be meaningless since the resource database needs to reside with the master database.

davmac1

I don't see a resource database.

bbrown

davmac1 wrote:

I don't see a resource database.

It's hidden. Look at the ..\MSSQL\data folder and you will see "mssqlsystemresource.mdf"

davmac1

Thanks - MS obvioiusly wants us mortal not to mess with it.

garak

My last note:

Please, take off "Alway Rowlock". This cost performance because the sql server must managed many row lock if there are big transactions instead few range locks or page locks. If you have disable this option (always rowlock) and you have now a lot of blocks, check first your C/AL, Index and SIFTs. If the changes on these three things have no effect, check also the system structure. If all go wrong :-( activate alway rowlock. But perfomance critical

stryk

Regarding "where are locks maintained":
I ran a perfmon analysis, monitoring tempdb, master and mssqlsystemresource looking for Logfile Usage, Transactions/sec, Lock Requests, etc. (Database is 300+GB, 100+ Users).
Well, there wasn't much activity on "master" and "mssqlsystemresource", anyway, nothing strange here ...
But: the counters "SQL Server:Locks - Lock Requests/sec (Total)" and "SQL Server:Databases - Transactions/sec (tempdb)" showed almost identical graphs - obviously there is a close relation!
According to this I could see an steady increase of Logfile Usage on "tempdb".
So, my conclusion would be that "locks" are maintained in "tempdb", generating real transactions there - or am I wrong?

davmac1

I think the most important issue is what and how many physical writes to and reads from disk are issued for databases other than the database NAV is using.
If it is all in RAM, then it should not matter if you have enough RAM.

stryk

davmac1 wrote:

I think the most important issue is what and how many physical writes to and reads from disk are issued for databases other than the database NAV is using.
If it is all in RAM, then it should not matter if you have enough RAM.

Yep, right. Unfortunately I/O could hardly be measured (separately) on this system as all those db are on the same SAN volume ...

davmac1

I will be at a 2 day NAV tuning class next week put on by one of NAV's SQL experts. (I have attended a few of his sessions at Convergence the last 2 years.)
I plan to ask him some of these questions. If you would like to suggest any others, forward them on to me.
I hope to have your book before I go - if I do, I will take it with me.

Nobody

Locks are held in RAM (96 bytes per lock). In x86 there is a max of 1.7 GB available for lock memory, sorting, and plan caching even if AWE is enabled. If you exceed the 1.7 GB limit on x86 SQL will begin to escalate granular locks to table level locks to free up memory. In x64 60% of the RAM (available to SQL) can be used for lock memory, sorting, and plan caching. "Always Rowlock" makes the most sense if you are running x64 and have suffcient RAM, but that is just my opinion.

Now if you use Read Committed Snapshot the TempDB will also be used to handle the "row versioning".