SQLTeam.com | Weblogs | Forums

Ssd vs scsi desn't any performance difference?!


#1

hello guys,
I've replace old SCSI disk with new SSD sata on HP proliant wth P420i controller (1Gb cache), server has 32Gbyte ram and DB has only 20Gbyte, benchmark test with SQLIO has very good result for SSD disk, it seems about 10 times better than scsi in read access (random and seq) but if I run a simple query on table with 4 milion rows it did result same as old scsi disk (2 minutes).

How can give me this result ?
Disk Alignment is correct, SQL server is Standard edition.

Thanks for any advice
Andrew


#2

Show us the query and the execution plan in XML format. I suspect your query or your indexes need to be modified. What is the query waiting on when you run it?


#3

the query simple select * from table, only for test benchmark


#4

Hi,
There are three factors you need to consider here:
1>> The size of your database
2>> The amount of memory you have in the server
3>> Your my.cnf configuration, specifically innodb_buffer_pool_size


#5

Limiting factor for that is probably the speed of the network between the server and your PC.

Query the data to a #TEMP table instead, so that there is virtually no server-to-client traffic - that should be faster.


#6

Your query is not a valid test. I bet it is seeing an ASYNC_NETWORK_IO wait since it's waiting on your desktop to keep sending the results.

I wouldn't even bother with a temp table. You need to run production-like queries, preferably in a load test.

Do you have a baseline load test for the original disks?


#7

Quey has been executed on local server, so there isn't possible network bottleneck.

I think all DB is loaded on ram, and query is the same performance. Is possible?
there is a command to know if table is loaded in memory or using disk access?


#8

Running it on the local server is not a good idea.

Yes the data is likely in memory already if you are repeatedly running it. You would need to drop clean buffers to clear it.

But I would stop what you are doing as this is not going to test your disks properly. You need real queries and not a select * query.