October 20, 2008 at 5:32 pm
hi everyone, I am having some problems comparing execution times between sql server 7.0 and sql 2k5. We have a sp that uses a cursor over 50000 records and inserts them into a different table according to some bussines logic defined for it. The thing is that when I have to insert this data on 2k5 I get really poor performance, and when I execute this on sql 7.0 it executes and performs really well, I checked with the activity monitor and I have a lot of time in write log waits.
Please help!!!!!!!!!!!!!!!!!!! 🙁
October 20, 2008 at 6:30 pm
Did you check the recovery mode of the databases on each server?
October 21, 2008 at 8:03 am
They are on simple recovery mode. When I was doing my tests it was on full recovery mode but then I tried changing it to simple, and continue running my tests but I got nothing achieved there 🙁
October 21, 2008 at 8:40 am
could be a lot of things, hardware, load on either server, disks, etc. check the plans, be sure they're running efficiently, look at load on the servers, check the speed of the disks, look for block errors.
Hard to diagnose it without messing around.
Does it scale linearly? Do 1000 records work the same on both systems? Indexing the same?
October 21, 2008 at 3:31 pm
Steve Jones - Editor (10/21/2008)
could be a lot of things, hardware, load on either server, disks, etc. check the plans, be sure they're running efficiently, look at load on the servers, check the speed of the disks, look for block errors.Hard to diagnose it without messing around.
Does it scale linearly? Do 1000 records work the same on both systems? Indexing the same?
They work the same with 1000 records but when we go over 200 thousand records we start getting writelogs delays in 2k5. They are both dedicated servers, they just query a table with no indexes and insert all the data into a new table with no indexes as well. Sorry but what do you mean with block errors? where do I look for them? :ermm: Thanks for the help. It's highly appreciated 😀
October 21, 2008 at 4:19 pm
Sorry, look for blocking. Mistyped earlier.
Are these two dedicated servers for testing or production? Can you answer on hardware?
October 21, 2008 at 5:16 pm
Agreed that it would be good to analyze the hardware. I would also like to see the IO latency associated with the two servers. Try running this query on both servers. The query was pulled from the Activity Monitor for SQL Server 2008 SSMS (ran trace to get the query). I tweaked it a bit. The create table has to be run first (it is commented out) and, the first time you run the query after the create table will yield no results. Following that you will get some good information. Sorry, haven't had time to clean it up yet.... 🙂
DECLARE @current_collection_time datetime;
SET @current_collection_time = GETDATE();
/*
CREATE TABLE #am_dbfilestats
(
collection_time datetime
, [Database] varchar(150)
, [File] varchar(500)
, [Total MB Read] numeric(18,2)
, [Total MB Written] numeric(18,2)
, [Total I/O Count] numeric(18,2)
, [Total I/O Wait Time (ms)] numeric(18,2)
, [Size (MB)] numeric(18,2)
)
*/
---- Grab a snapshot
INSERT INTO #am_dbfilestats
SELECT
@current_collection_time AS collection_time,
d.name AS [Database],
f.physical_name AS [File],
(fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read],
(fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written],
(fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count],
fs.io_stall AS [Total I/O Wait Time (ms)],
fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)]
FROM sys.dm_io_virtual_file_stats(default, default) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id
INNER JOIN sys.databases d ON d.database_id = fs.database_id;
-- Get the timestamp of the previous collection time
DECLARE @previous_collection_time datetime;
SELECT TOP 1 @previous_collection_time = collection_time
FROM #am_dbfilestats
WHERE collection_time < @current_collection_time
ORDER BY collection_time DESC;
DECLARE @interval_ms int;
SET @interval_ms = DATEDIFF (millisecond, @previous_collection_time, @current_collection_time);
-- Return the diff of this snapshot and last
SELECT
cur.[Database],
cur.[File] AS [File Name],
CONVERT (numeric(28,1), (cur.[Total MB Read] - prev.[Total MB Read]) * 1000 / @interval_ms) AS [MB/sec Read],
CONVERT (numeric(28,1), (cur.[Total MB Written] - prev.[Total MB Written]) * 1000 / @interval_ms) AS [MB/sec Written],
-- protect from div-by-zero
CASE
WHEN (cur.[Total I/O Count] - prev.[Total I/O Count]) = 0 THEN 0
ELSE
ROUND((cur.[Total I/O Wait Time (ms)] - prev.[Total I/O Wait Time (ms)])
/ (cur.[Total I/O Count] - prev.[Total I/O Count]), 1)
END AS [Response Time (ms)]
FROM #am_dbfilestats AS cur
INNER JOIN #am_dbfilestats AS prev ON prev.[Database] = cur.[Database] AND prev.[File] = cur.[File]
WHERE cur.collection_time = @current_collection_time
AND prev.collection_time = @previous_collection_time
ORDER BY
[Response Time (ms)] DESC;
-- Delete the older snapshot
DELETE FROM #am_dbfilestats
WHERE collection_time != @current_collection_time;
--DROP TABLE #am_dbfilestats
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 21, 2008 at 6:01 pm
Check the autoshrink, autogrow settings on sql 2k5.
If the files need to grow during the process, that'll hurt a lot (as much as 40-50% slower). Autoshrink could add fragmentation on the physical files, compounding the problem.
I've seen fragmentations cause a similar problem, altough only 2-3 times slower... you'll still have to analyse the hardware.
October 22, 2008 at 10:01 am
How did you get the data from SQL7 to SQL 2005? If you copied the mdf and ldf files, or if you did a restore, you need to rebuild ststistics on SQL 2005 befoer you run any performance tests.
Also, have you set SQL Server maximum memory to something reasonable for your server. You need to allow at least 1 GB for the operating system, and depending on your Anti-Virus and other stuff often you need 2 GB or more.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 22, 2008 at 11:25 am
Ninja's_RGR'us (10/21/2008)
Check the autoshrink, autogrow settings on sql 2k5.If the files need to grow during the process, that'll hurt a lot (as much as 40-50% slower). Autoshrink could add fragmentation on the physical files, compounding the problem.
I've seen fragmentations cause a similar problem, altough only 2-3 times slower... you'll still have to analyse the hardware.
Hi everyone, thanks for your suggestions I really appreciate your help. Well they are both dedicated servers and they both production servers.
My SQL Server 7.0 is running on Microsoft Windows NT 4.0, we use for this server an IBM NetFinity 6000r (rack) server with 1GB RAM, 40 GB. HD (Redundant SAN) and 2 Proccesors Pentium Xeon III 700 Mhz.
Now my SQL Server 2k5 is running on Windows 2003 Server Enterprise Edition. It has one 2.33 GHz Quad-Core Intel Xeon with 4 GB RAM and 146GB of hard drive. My HD where 2k5 is installes is installed with RAID 5.
Thanks again for the help. One thing I didn't take in count is the autogrowth property. It's probably set to a really low value for all the transactions we record. I'll try that out 😀
Yeah I did a restoration for my database, and I did rebuild all the indexes that needed to. My sql server 2k5 is using 1.9GB RAM most of the time, we reboot our servers every sunday for maintance, any extra ideas?
October 22, 2008 at 1:27 pm
I don't know if it's possible, but to repair the framgentation problems, you need to defrag the drive with the DB detached (otherwise defrag won't touch those files). If that's not possible, you can always create a ghost copy of the database, with all the files at their full size before loading the data in. Then see if that makes a difference. Make sure to update stats with fullscan before testing the performance again, and also make sure that indexes are not frangmented.
Also it's always a good idea to have the db files on a different drive than the OS and programs... hard to tell if that's the case with what you told in your last post... even better if the logs are also on a separate drive.
October 22, 2008 at 1:33 pm
You didn't mention the drive configuration on the first server other than SAN. How many disks in the volume, what is the RAID configuration for that drive? On the new server you stated that it was RAID 5 with 146 GB of storage but how many disks in that array? Is that on a SAN as well or is it Direct Attached Storage?
Were you able to run that query against the two instances during your processing to see what the IO response times were?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 28, 2008 at 2:36 pm
DavidB (10/22/2008)
You didn't mention the drive configuration on the first server other than SAN. How many disks in the volume, what is the RAID configuration for that drive? On the new server you stated that it was RAID 5 with 146 GB of storage but how many disks in that array? Is that on a SAN as well or is it Direct Attached Storage?Were you able to run that query against the two instances during your processing to see what the IO response times were?
Finally I got time to run this tests, well my tempdb' mdf file had for MB/sec Read 139, MB/sec Written 0.0 and it's Response Time (ms) was 36. My tempdb' ldf had MB/sec Read 0.0, MB/sec Written 11.3 and it's Response Time (ms) was 14.7. For the the database's mdf file where I do the work I got MB/sec Read 0.0, MB/sec Written 12.5 and it's Response Time (ms) was 28. And for it's ldf file I got MB/sec Read 0.0, MB/sec Written 3.1 and it's Response Time (ms) was 46. Now their lowest values are 0,0,0.
Now for my SQL Server 7.0 has 3 disks using RAID 5 configured as a SAN, my SQL Server 2k5 has only one disk but it's supposed to be able to do parallel read/write operations. Another consideration to this is that on my server where my SqlServer 7.0 is, there are a lot of other applications,databases working there and on my server where my sql server 2k5 there is onlye sql server 2k5 installed.
Thank you very much in advance.
October 28, 2008 at 2:47 pm
chileu17 (10/28/2008)
my SQL Server 2k5 has only one disk but it's supposed to be able to do parallel read/write operations.
I would be a bit concerned about this configuration even though your IO information doesn't look concerning. A bit odd that the configuration is set up this way honestly. I am hoping that this is not a production server?.?...?
Regardless, as Steve mentioned some time ago if the servers are similarly configured, processor, memory, etc you shouldn't have experienced a decrease in performance. You MIGHT have hit an interesting spot in the query processor but at this point with 2005 being as aged as it is I doubt that as well. The disk configuration would certainly be something that I would consider as a priority to get fixed. Just my thoughts.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply