December 24, 2007 at 6:18 am
We have one general problem with upgrading databases from sql2000 to sql2005. And main issue is performance 🙂
Problem is that db which on sql2000 was working quite nice, upon transfering to sql2005 program obviously slows down (>20%).
There is one interestiong thing: in situation when Server2005 and client are on same machine, slow down is not so obvious. As soon as one machine tries to access server through network we have problems.
Another issue is that quite often we have slow down on some simple querries, but some more complicated querries work fine.
We tried this on several sistems, and most extreme so far is situation is DB transfer with server upgrade:
Config1: PentIV@2400MHz
1 GB DDR2
sata hdd 160MB
upgraded to
Config2: Pentium 4 Dual Core 3GHz
2 GB DDR2
2x sata2 320gb raid1
And complains from operators are unbareable
BTW: since Express, by default, goes with disabled network access, we just turn off disable network protocol, simmilar as in sql2000 database desktop engines.
:hehe:
December 24, 2007 at 6:47 am
SQLExpress will at most use 1 Gb of ram
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
- did you perform full dbmaintenance after setup ?
(dbcc dbreindex , sp_updatestats, dbcc updateusage)
- -- checkdb with content-re-eveluation
DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY;
http://support.microsoft.com/kb/923247 !!!
- Can you see any issues with windows perfmon ?
- what kind of connections are your users performing. (By default they may be with isolation level repeatable read (worse case) So you may end up with very much lock overhead.
-you may want to start a local trace with sqlexpress to see what's going on . Use sqlprofiler if you have it available or start a trace ising sqlcmd.
To capture data to the server's c-drive you could use:
-- show Traces info
SELECT * FROM :: fn_trace_getinfo(default)
goto finish -- place in comment to start a trace !
declare @StartStopEnd varchar(5)
declare @TraceID int
declare @RunDays smallint
declare @RunHours smallint
declare @RunMinutes smallint
declare @PlanNY char(1)
declare @PlanXMLNY char(1)
-- standaard voor 15 minuten ingesteld
Select @RunDays = 0
, @RunHours = 1
, @RunMinutes = 15
Select @StartStopEnd = 'Start', @TraceID = 0, @PlanNY = 'N', @PlanXMLNY='N'
-- Select @StartStopEnd = 'Stop', @TraceID = 2
-- Select @StartStopEnd = 'End' --, @TraceID = xxx
if upper(@StartStopEnd )= 'START' goto StartTrace
else
begin
if upper(@StartStopEnd )= 'STOP' goto StopTrace
else
begin
if upper(@StartStopEnd )= 'END' goto CleanupTrace
else
begin
Print 'Ongeldige @StartStopEnd kode [' + @StartStopEnd + ']'
goto finish
end
end
end
goto finish
StartTrace:
-- Create a Queue
declare @rc int
-- declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = dateadd(dd,@RunDays,dateadd(hh,@RunHours,dateadd(mi,@RunMinutes,getdate())))
-- MB
set @maxfilesize = 50 -- Mb
Declare @TraceFileName nvarchar(300)
set @TraceFileName = 'C:\ALZDBA_SQL_Trace' + '_' + replace(@@servername,'\','_') + '_' + replace(replace(replace(convert(char(16),getdate(),121),'-',''),' ','_'),':','') + '_' -- + '.trc' wordt automatisch toegevoegd
if datalength(@TraceFileName) > 299
begin
print 'Filename to long ! [' + cast( datalength(@TraceFileName) as varchar(5)) +'] - [' + cast(@TraceFileName as varchar(300)) +']'
goto finish
end
print '-- Saving tracedata to : ' + cast(@TraceFileName as varchar(300)) + ' --'
print '-- **********************' + REPLICATE('*', datalength(@TraceFileName)/2) + ' --'
print ' '
print '-- Trace will end at ' + convert(varchar(23),@DateTime,121) + '. --'
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
--exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
exec @rc = sp_trace_create @TraceID output, 2, @TraceFileName, @maxfilesize, @Datetime
if (@rc <> 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
-- 10 = RPC:Completed
exec sp_trace_setevent @TraceID, 10, 1, @on -- textdata
exec sp_trace_setevent @TraceID, 10, 3, @on -- DbId
exec sp_trace_setevent @TraceID, 10, 6, @on -- NTUsername
exec sp_trace_setevent @TraceID, 10, 7, @on -- NTDomainname
exec sp_trace_setevent @TraceID, 10, 8, @on -- ClientHostname
exec sp_trace_setevent @TraceID, 10, 10, @on -- Applicationname
exec sp_trace_setevent @TraceID, 10, 11, @on -- SQLSecurityLoginName
exec sp_trace_setevent @TraceID, 10, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 10, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 10, 14, @on -- Starttime
exec sp_trace_setevent @TraceID, 10, 15, @on -- Endtime
exec sp_trace_setevent @TraceID, 10, 16, @on -- Reads
exec sp_trace_setevent @TraceID, 10, 17, @on -- Writes
exec sp_trace_setevent @TraceID, 10, 18, @on -- CPU
exec sp_trace_setevent @TraceID, 10, 26, @on -- Servername
exec sp_trace_setevent @TraceID, 10, 31, @on -- Error
exec sp_trace_setevent @TraceID, 10, 40, @on -- DatabaseUsername
-- 11 = RPC:Starting
exec sp_trace_setevent @TraceID, 11, 1, @on -- textdata
exec sp_trace_setevent @TraceID, 11, 3, @on -- DbId
exec sp_trace_setevent @TraceID, 11, 6, @on -- NTUsername
exec sp_trace_setevent @TraceID, 11, 7, @on -- NTDomainname
exec sp_trace_setevent @TraceID, 11, 8, @on -- ClientHostname
exec sp_trace_setevent @TraceID, 11, 10, @on -- Applicationname
exec sp_trace_setevent @TraceID, 11, 11, @on -- SQLSecurityLoginName
exec sp_trace_setevent @TraceID, 11, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 11, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 11, 14, @on -- Starttime
exec sp_trace_setevent @TraceID, 11, 15, @on -- Endtime
exec sp_trace_setevent @TraceID, 11, 16, @on -- Reads
exec sp_trace_setevent @TraceID, 11, 17, @on -- Writes
exec sp_trace_setevent @TraceID, 11, 18, @on -- CPU
exec sp_trace_setevent @TraceID, 11, 26, @on -- Servername
exec sp_trace_setevent @TraceID, 11, 31, @on -- Error
exec sp_trace_setevent @TraceID, 11, 40, @on -- DatabaseUsername
-- 12 = SQL:BatchCompleeted
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 31, @on -- Error
exec sp_trace_setevent @TraceID, 12, 40, @on
-- 13 = SQL:BatchStarting
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 13, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 15, @on
exec sp_trace_setevent @TraceID, 13, 16, @on
exec sp_trace_setevent @TraceID, 13, 17, @on
exec sp_trace_setevent @TraceID, 13, 18, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 31, @on -- Error
exec sp_trace_setevent @TraceID, 13, 40, @on
-- 14 = Login
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 7, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 15, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 31, @on -- Error
exec sp_trace_setevent @TraceID, 14, 40, @on
-- 15 = Logout
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 7, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 26, @on
exec sp_trace_setevent @TraceID, 15, 31, @on -- Error
exec sp_trace_setevent @TraceID, 15, 40, @on
-- 17 = ExistingConnection
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 7, @on
exec sp_trace_setevent @TraceID, 17, 8, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 13, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 15, @on
exec sp_trace_setevent @TraceID, 17, 16, @on
exec sp_trace_setevent @TraceID, 17, 17, @on
exec sp_trace_setevent @TraceID, 17, 18, @on
exec sp_trace_setevent @TraceID, 17, 26, @on
exec sp_trace_setevent @TraceID, 17, 31, @on -- Error
exec sp_trace_setevent @TraceID, 17, 40, @on
if @PlanNY = 'Y'
Begin
-- 68 = obtain the execution plans
exec sp_trace_setevent @traceid, 68, 1, @on --Execution Plan, TextData
-- exec sp_trace_setevent @traceid, 68, 2, @on --Execution Plan, BinaryData
exec sp_trace_setevent @traceid, 68, 3, @on --Execution Plan, DatabaseID
-- exec sp_trace_setevent @traceid, 68, 4, @on --Execution Plan, TransactionID
exec sp_trace_setevent @traceid, 68, 6, @on --Execution Plan, NTUserName
exec sp_trace_setevent @traceid, 68, 7, @on --Execution Plan, NTDomainName
exec sp_trace_setevent @traceid, 68, 8, @on --Execution Plan, ClientHostName
-- exec sp_trace_setevent @traceid, 68, 9, @on --Execution Plan, ClientProcessID
exec sp_trace_setevent @traceid, 68, 10, @on --Execution Plan, ApplicationName
exec sp_trace_setevent @traceid, 68, 11, @on --Execution Plan, SQLSecurityLoginName
exec sp_trace_setevent @traceid, 68, 12, @on --Execution Plan, SPID
exec sp_trace_setevent @traceid, 68, 13, @on --Execution Plan, Duration
exec sp_trace_setevent @traceid, 68, 14, @on --Execution Plan, StartTime
exec sp_trace_setevent @traceid, 68, 15, @on --Execution Plan, EndTime
exec sp_trace_setevent @traceid, 68, 16, @on --Execution Plan, Reads
exec sp_trace_setevent @traceid, 68, 17, @on --Execution Plan, Writes
exec sp_trace_setevent @traceid, 68, 18, @on --Execution Plan, CPU
-- exec sp_trace_setevent @traceid, 68, 21, @on --Execution Plan, EventSubClass
-- exec sp_trace_setevent @traceid, 68, 22, @on --Execution Plan, ObjectID
-- exec sp_trace_setevent @traceid, 68, 25, @on --Execution Plan, IntegerData
-- exec sp_trace_setevent @traceid, 68, 27, @on --Execution Plan, Eventclass
exec sp_trace_setevent @TraceID, 68, 26, @on
exec sp_trace_setevent @TraceID, 68, 31, @on -- Error
exec sp_trace_setevent @TraceID, 68, 40, @on
-- --Showplan All, Eventclass
--exec sp_trace_setevent @TraceID, 97, 9, @on
---- exec sp_trace_setevent @TraceID, 97, 2, @on
--exec sp_trace_setevent @TraceID, 97, 10, @on
--exec sp_trace_setevent @TraceID, 97, 14, @on
--exec sp_trace_setevent @TraceID, 97, 11, @on
--exec sp_trace_setevent @TraceID, 97, 12, @on
exec sp_trace_setevent @traceid, 97, 1, @on --Showplan All, TextData
-- exec sp_trace_setevent @traceid, 97, 2, @on --Showplan All, BinaryData
exec sp_trace_setevent @traceid, 97, 3, @on --Showplan All, DatabaseID
-- exec sp_trace_setevent @traceid, 97, 4, @on --Showplan All, TransactionID
exec sp_trace_setevent @traceid, 97, 9, @on --Showplan All, ClientProcessID
exec sp_trace_setevent @traceid, 97, 11, @on --Showplan All, SQLSecurityLoginName
exec sp_trace_setevent @traceid, 97, 12, @on --Showplan All, SPID
exec sp_trace_setevent @TraceID, 97, 26, @on
exec sp_trace_setevent @TraceID, 97, 31, @on -- Error
exec sp_trace_setevent @TraceID, 97, 40, @on
END
if @PlanXMLNY = 'Y'
BEGIN
-- -- Showplan XML
--exec sp_trace_setevent @TraceID, 122, 1, @on
--exec sp_trace_setevent @TraceID, 122, 3, @on
--exec sp_trace_setevent @TraceID, 122, 11, @on
--exec sp_trace_setevent @TraceID, 122, 35, @on
--exec sp_trace_setevent @TraceID, 122, 12, @on
--exec sp_trace_setevent @TraceID, 122, 28, @on
--
---- 146 Showplan XML Statistics
--exec sp_trace_setevent @TraceID, 146, 1, @on
--exec sp_trace_setevent @TraceID, 146, 3, @on
--exec sp_trace_setevent @TraceID, 146, 11, @on
--exec sp_trace_setevent @TraceID, 146, 35, @on
--exec sp_trace_setevent @TraceID, 146, 12, @on
--exec sp_trace_setevent @TraceID, 146, 28, @on
--
-- 168 Showplan XML For Query
exec sp_trace_setevent @TraceID, 168, 1, @on
exec sp_trace_setevent @TraceID, 168, 3, @on
exec sp_trace_setevent @TraceID, 168, 11, @on
exec sp_trace_setevent @TraceID, 168, 35, @on
exec sp_trace_setevent @TraceID, 168, 12, @on
exec sp_trace_setevent @TraceID, 168, 28, @on
---- 169 Showplan All
--exec sp_trace_setevent @TraceID, 169, 1, @on
--exec sp_trace_setevent @TraceID, 169, 3, @on
--exec sp_trace_setevent @TraceID, 169, 11, @on
--exec sp_trace_setevent @TraceID, 169, 35, @on
--exec sp_trace_setevent @TraceID, 169, 12, @on
--exec sp_trace_setevent @TraceID, 169, 28, @on
END
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, @status = 1
-- display trace id for future references
-- select TraceID=@TraceID
print '-- '
Print '-- TraceID for this trace [' + cast(@TraceID as varchar(10)) + '] --'
Print '-- ************************' + REPLICATE('*', datalength(cast(@TraceID as varchar(10)))) + '* --'
-- show Traces info
SELECT * FROM :: fn_trace_getinfo(default)
goto finish
StopTrace:
-- Set the trace status to stop
exec sp_trace_setstatus @TraceID, @status = 0
print 'Trace Stopped.'
goto finish
CleanupTrace:
-- Set the trace status to cleanup
exec sp_trace_setstatus @TraceID, @status = 2
print 'Trace Cleaned up.'
goto finish
error:
select ErrorCode=@rc
finish:
go
You can then import the captured data using e.g.:
select *
into yourtracetable
from ::fn_trace_gettable('c:\thetracefilename.trc' , default)
Select db_name(DatabaseID) as DatabaseName, NtDomainName, NTUserName, HostName, ApplicationName, LoginName, StartTime, EndTime, ObjectId, ServerName
,*
from yourtracetable
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 and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
December 24, 2007 at 10:06 am
The big one is probably being sure indexes are ok and statistics are up to date.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply