SQL Server Performance Forum – Threads Archive

Sql 2005 Inserting

Hey,
We do rapid and mostly inserting. We are testing a 2005 box which processes way slower than our 2000 box, although the 2005 box is much better (faster cpus etc). The only thing is the 2000 box has 2meg L2 cache and the 2005 has 1meg L2 cache. We use the TempDB a lot. I know 2005 uses the tempdb more than 2000. Do you think moving all of the tempdb work to a new separate db would help although now it will be doing more logging? I’ve done all the optimizations I know of and our 2005 box just keeps getting killed by the 2000 box. It will be a LOT of work, I just want to know what you guys think first. Thanks!

---

i suggest a proper analysis of the system to determine where the delays are,
instead of just guessing and trying various things the most common cause of the symptom you are seeing is a difference in the OS settings for caching on the physical disk. in Computer Management -> Disk Management, right click on Disk0 or whatever disk your log resides on, go to the policies tab, and compare settings between each system
also relevent is if either system has a hardware RAID controller, which might disable the OS setting, but have an equivalent in its own config tool

---

Hey Joe!
I can’t find the policies tab you are mentioning. I’m at the disk manager, and then I right click and did properties, but didn’t see caching. We have 3 raid 0s setup. The temp is on one, DB main file on one and DB log file on another. It’s all hardward RAID.
Thanks!
Justin

---

can you create a baseline and see the difference?
run the script on both machine and see the difference:
PRINT ‘————————————————————————–‘
PRINT ‘-= Performance Baseline Generator v1.0 =-‘
PRINT ‘————————————————————————–‘
PRINT ‘Author: Perez, Jeffrey M’
PRINT ‘Date: ‘ + CONVERT(VARCHAR, GETDATE(), 100)
PRINT ‘Server: ‘+ CAST(SERVERPROPERTY(‘ServerName’) AS VARCHAR)
PRINT ‘User: ‘ + SUSER_SNAME() + ‘, ‘ + CURRENT_USER
PRINT ‘Workstation: ‘ + HOST_NAME()
PRINT ‘Version Info:’
PRINT ‘ ‘ + REPLACE(@@VERSION,CHAR(9),’ ‘)
PRINT ‘————————————————————————–‘
GO
PRINT ‘Running Test…’
GO
IF (SELECT OBJECT_ID(‘test’)) IS NOT NULL DROP TABLE test
GO
CREATE TABLE test(
SAID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DATA CHAR(8000)
)
GO
DECLARE
@Counter INT,
@Start DATETIME,
@End DATETIME,
@Limit INT SET NOCOUNT ON
SET @Start = GETDATE()
SET @Counter = 1
SET @Limit = 10000
WHILE @Counter < @Limit
BEGIN
INSERT INTO TEST(DATA)
SELECT SPACE(8000) SET @Counter = @Counter + 1
END
SET @End = GETDATE()
PRINT ‘Test Complete.’
PRINT ”
PRINT ‘Database: ‘ + DB_NAME()
PRINT ‘Start Time:’ + CONVERT(VARCHAR, @Start,114)
PRINT ‘End Time:’ + CONVERT(VARCHAR, @End,114)
PRINT ‘Baseline Mark: ‘ + LTRIM(STR(@Limit/DATEDIFF(ss, @Start, @End))) + ‘ transaction/s’
GO
IF (SELECT OBJECT_ID(‘test’)) IS NOT NULL DROP TABLE test
GO
PRINT ‘————————————————————————–‘
May the Almighty God bless us all!
www.empoweredinformation.com

---

Hey cmdr_skywalker!
Thanks for the script!!!!!!
I will run it when things are done for the day. J

---

Glad to be of service <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />. Let us know what else we can do for you.<br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com

---

]]>