Dual Xeon 3.8GHz x64 processors with SQL Server 2000 + Windows Server 2003...performance hits
-
offcourse .... your previous reply got me back to hinking ....
use yourdb
go
DECLARE @TableName nvarchar(261)
DECLARE @SQLStatement nvarchar(4000)
DECLARE TableList CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE EXISTS
(
SELECT *
FROM sysindexes
WHERE id =
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)) AND
indid IN(0,1)
)
OPEN TableList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TableList INTO @TableName
IF @@FETCH_STATUS = -1 BREAK
RAISERROR ('Reindexing %s', 0, 1, @TableName) WITH NOWAIT
set @SQLStatement = 'DBCC DBREINDEX (''['+ db_name() + '].' + @TableName + ''')'
EXEC(@SQLStatement)
-- print @SQLStatement
END
CLOSE TableList
DEALLOCATE TableList
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Running that right now, as no one is using this system yet. I'll let you know what happens.
-
Does not appear to have gotten any better. Any other ideas?
-
did you use sql-profiler to look what's going on ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I sent a trace to our application programmers (trace included the execution plan traces).
-
If you have it captured to file , save the file to a sqlserver and launch queries against that table.
This way you have a quick overview of your large consumers queries !
Maybe that gets you back on track.
fwiw : I always have to provide extra support to analyse trace info. an extra couple of eyes with an unpoisoned view (application wize) is mostly a big help.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I think you need to check that parallelism is not your problem, you can check this with the query plan in QA or failing that adding maxdop 1 to your sql statements.
I do testing, generally on twin ( no HT ) and 4 way ( with HT ) boxes and compare against my prod ( 8 real = 16 with HT ) to make sure. I've never disabled HT or set processor masks but I have used maxdop in a few places where I couldn't improve the queries for one reason or another. I don't find any particular difference between w2003 and w2k, if anythign w2003 possibly has the edge. I'd really suggest you check out the query plans.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
sorry - just wanted to say that I can't compare 64 bit as I don't have one of those ( yet ) to play with, but I'm told by a few dba's who do have 64bit that it flies!!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply