|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Sql 2005 ProfilerTest Database Server = Server 1 It’s a dual 1800 AMD, 2 Gig or RAM Sql 2000 on the default instance Sql 2005 running on another instance Not running them both at the same time Both have a max memory setting of 1800 Mb I’m using a trc file to do the load testing through profiler. The trc was created using Sql 2000 profiler going against a database I’ll call Db2000. I’ve done a timed test of running through the entire trc file. Sql 2000 is just slightly slower than 2005 (I did a reindex, stats, recompile) Ok.. here is my issue that I’m trying to figure out. When I run this trc file through sql 2005 profiler on client Test1 hitting the database on Server1 and recording the results to Server 2 (I’m only grabbing statements with a duration greater than 5), I’m getting sql statements that have a duration of over 3 million. I run the that same sql statement in Management Studio and it takes 262. So I’m trying to figure out why profiler is seeing the duration as over 3 million milliseconds. The trace file to run in total is never more than 15 minutes total.. so for 1 statement to say it’s taking just over 50 minutes.. I just doesn’t make sense that it’s right. Anyone seen this. -- Shannon Ramirez Network DBA There's a setting in 2005 Profiler to show durations in mictroseconds instead of milliseconds.
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "jvcoach23" <jvcoac***@discussions.microsoft.com> wrote in message news:BF7EBEE6-4B5A-4AD3-98AF-ECBCFBC716B6@microsoft.com... > I've been doing some time test between Sql 2000 sp3a and Sql 2005. > > > > Test Database Server = Server 1 > > It's a dual 1800 AMD, 2 Gig or RAM > > Sql 2000 on the default instance > > Sql 2005 running on another instance > > Not running them both at the same time > > Both have a max memory setting of 1800 Mb > > > > > > I'm using a trc file to do the load testing through profiler. The trc was > created using Sql 2000 profiler going against a database I'll call Db2000. > > > > I've done a timed test of running through the entire trc file. Sql 2000 is > just slightly slower than 2005 (I did a reindex, stats, recompile) > > > > Ok.. here is my issue that I'm trying to figure out. When I run this trc > file through sql 2005 profiler on client Test1 hitting the database on > Server1 and recording the results to Server 2 (I'm only grabbing statements > with a duration greater than 5), I'm getting sql statements that have a > duration of over 3 million. I run the that same sql statement in Management > Studio and it takes 262. > > > > So I'm trying to figure out why profiler is seeing the duration as over 3 > million milliseconds. The trace file to run in total is never more than 15 > minutes total.. so for 1 statement to say it's taking just over 50 minutes.. > I just doesn't make sense that it's right. > > > > Anyone seen this. > > > > > > -- > Shannon Ramirez > Network DBA So are you saying that you think it's showing the time in microseconds...
because I want to be using the same metric between sql 2000 and 2005 in my testing so that I can see what sp's or sql statements are taking the longest amount of time.. so that I can compare the two..??? thanks -- Show quoteShannon Ramirez Network DBA "Tibor Karaszi" wrote: > There's a setting in 2005 Profiler to show durations in mictroseconds instead of milliseconds. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "jvcoach23" <jvcoac***@discussions.microsoft.com> wrote in message > news:BF7EBEE6-4B5A-4AD3-98AF-ECBCFBC716B6@microsoft.com... > > I've been doing some time test between Sql 2000 sp3a and Sql 2005. > > > > > > > > Test Database Server = Server 1 > > > > It's a dual 1800 AMD, 2 Gig or RAM > > > > Sql 2000 on the default instance > > > > Sql 2005 running on another instance > > > > Not running them both at the same time > > > > Both have a max memory setting of 1800 Mb > > > > > > > > > > > > I'm using a trc file to do the load testing through profiler. The trc was > > created using Sql 2000 profiler going against a database I'll call Db2000. > > > > > > > > I've done a timed test of running through the entire trc file. Sql 2000 is > > just slightly slower than 2005 (I did a reindex, stats, recompile) > > > > > > > > Ok.. here is my issue that I'm trying to figure out. When I run this trc > > file through sql 2005 profiler on client Test1 hitting the database on > > Server1 and recording the results to Server 2 (I'm only grabbing statements > > with a duration greater than 5), I'm getting sql statements that have a > > duration of over 3 million. I run the that same sql statement in Management > > Studio and it takes 262. > > > > > > > > So I'm trying to figure out why profiler is seeing the duration as over 3 > > million milliseconds. The trace file to run in total is never more than 15 > > minutes total.. so for 1 statement to say it's taking just over 50 minutes.. > > I just doesn't make sense that it's right. > > > > > > > > Anyone seen this. > > > > > > > > > > > > -- > > Shannon Ramirez > > Network DBA > > > just to make sure i'm being complete.. i checked and in the options it does
say that it is displaying it in milliseconds.. that is unless perhaps it changes that value if you choose a template.. -- Show quoteShannon Ramirez Network DBA "Tibor Karaszi" wrote: > There's a setting in 2005 Profiler to show durations in mictroseconds instead of milliseconds. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "jvcoach23" <jvcoac***@discussions.microsoft.com> wrote in message > news:BF7EBEE6-4B5A-4AD3-98AF-ECBCFBC716B6@microsoft.com... > > I've been doing some time test between Sql 2000 sp3a and Sql 2005. > > > > > > > > Test Database Server = Server 1 > > > > It's a dual 1800 AMD, 2 Gig or RAM > > > > Sql 2000 on the default instance > > > > Sql 2005 running on another instance > > > > Not running them both at the same time > > > > Both have a max memory setting of 1800 Mb > > > > > > > > > > > > I'm using a trc file to do the load testing through profiler. The trc was > > created using Sql 2000 profiler going against a database I'll call Db2000. > > > > > > > > I've done a timed test of running through the entire trc file. Sql 2000 is > > just slightly slower than 2005 (I did a reindex, stats, recompile) > > > > > > > > Ok.. here is my issue that I'm trying to figure out. When I run this trc > > file through sql 2005 profiler on client Test1 hitting the database on > > Server1 and recording the results to Server 2 (I'm only grabbing statements > > with a duration greater than 5), I'm getting sql statements that have a > > duration of over 3 million. I run the that same sql statement in Management > > Studio and it takes 262. > > > > > > > > So I'm trying to figure out why profiler is seeing the duration as over 3 > > million milliseconds. The trace file to run in total is never more than 15 > > minutes total.. so for 1 statement to say it's taking just over 50 minutes.. > > I just doesn't make sense that it's right. > > > > > > > > Anyone seen this. > > > > > > > > > > > > -- > > Shannon Ramirez > > Network DBA > > > From BOL
"In SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds." ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/17e821ca-a12e-4192-acc1-96765d9ae266.htm Show quote "jvcoach23" <jvcoac***@discussions.microsoft.com> wrote in message news:BF7EBEE6-4B5A-4AD3-98AF-ECBCFBC716B6@microsoft.com... > I've been doing some time test between Sql 2000 sp3a and Sql 2005. > > > > Test Database Server = Server 1 > > It's a dual 1800 AMD, 2 Gig or RAM > > Sql 2000 on the default instance > > Sql 2005 running on another instance > > Not running them both at the same time > > Both have a max memory setting of 1800 Mb > > > > > > I'm using a trc file to do the load testing through profiler. The trc was > created using Sql 2000 profiler going against a database I'll call Db2000. > > > > I've done a timed test of running through the entire trc file. Sql 2000 > is > just slightly slower than 2005 (I did a reindex, stats, recompile) > > > > Ok.. here is my issue that I'm trying to figure out. When I run this trc > file through sql 2005 profiler on client Test1 hitting the database on > Server1 and recording the results to Server 2 (I'm only grabbing > statements > with a duration greater than 5), I'm getting sql statements that have a > duration of over 3 million. I run the that same sql statement in > Management > Studio and it takes 262. > > > > So I'm trying to figure out why profiler is seeing the duration as over 3 > million milliseconds. The trace file to run in total is never more than > 15 > minutes total.. so for 1 statement to say it's taking just over 50 > minutes.. > I just doesn't make sense that it's right. > > > > Anyone seen this. > > > > > > -- > Shannon Ramirez > Network DBA great.. that answers it for me.. a nugget in BOL.. sorry... was just looking
in the wrong spot i guess -- Show quoteShannon Ramirez Network DBA "Jasper Smith" wrote: > From BOL > > "In SQL Server 2005, the server reports the duration of an event in > microseconds (one millionth, or 10-6, of a second) and the amount of CPU > time used by the event in milliseconds (one thousandth, or 10-3, of a > second). In SQL Server 2000, the server reported both duration and CPU time > in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user > interface displays the Duration column in milliseconds by default, but when > a trace is saved to either a file or a database table, the Duration column > value is written in microseconds." > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/17e821ca-a12e-4192-acc1-96765d9ae266.htm > > -- > HTH, > Jasper Smith (SQL Server MVP) > http://www.sqldbatips.com > > > "jvcoach23" <jvcoac***@discussions.microsoft.com> wrote in message > news:BF7EBEE6-4B5A-4AD3-98AF-ECBCFBC716B6@microsoft.com... > > I've been doing some time test between Sql 2000 sp3a and Sql 2005. > > > > > > > > Test Database Server = Server 1 > > > > It's a dual 1800 AMD, 2 Gig or RAM > > > > Sql 2000 on the default instance > > > > Sql 2005 running on another instance > > > > Not running them both at the same time > > > > Both have a max memory setting of 1800 Mb > > > > > > > > > > > > I'm using a trc file to do the load testing through profiler. The trc was > > created using Sql 2000 profiler going against a database I'll call Db2000. > > > > > > > > I've done a timed test of running through the entire trc file. Sql 2000 > > is > > just slightly slower than 2005 (I did a reindex, stats, recompile) > > > > > > > > Ok.. here is my issue that I'm trying to figure out. When I run this trc > > file through sql 2005 profiler on client Test1 hitting the database on > > Server1 and recording the results to Server 2 (I'm only grabbing > > statements > > with a duration greater than 5), I'm getting sql statements that have a > > duration of over 3 million. I run the that same sql statement in > > Management > > Studio and it takes 262. > > > > > > > > So I'm trying to figure out why profiler is seeing the duration as over 3 > > million milliseconds. The trace file to run in total is never more than > > 15 > > minutes total.. so for 1 statement to say it's taking just over 50 > > minutes.. > > I just doesn't make sense that it's right. > > > > > > > > Anyone seen this. > > > > > > > > > > > > -- > > Shannon Ramirez > > Network DBA > > > It has caught me out before when looking at loaded trace tables :-)
Show quote "jvcoach23" <jvcoac***@discussions.microsoft.com> wrote in message news:C9C46741-6272-427D-903D-4101BEBF2876@microsoft.com... > great.. that answers it for me.. a nugget in BOL.. sorry... was just > looking > in the wrong spot i guess > -- > Shannon Ramirez > Network DBA > > > "Jasper Smith" wrote: > >> From BOL >> >> "In SQL Server 2005, the server reports the duration of an event in >> microseconds (one millionth, or 10-6, of a second) and the amount of CPU >> time used by the event in milliseconds (one thousandth, or 10-3, of a >> second). In SQL Server 2000, the server reported both duration and CPU >> time >> in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical >> user >> interface displays the Duration column in milliseconds by default, but >> when >> a trace is saved to either a file or a database table, the Duration >> column >> value is written in microseconds." >> >> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/17e821ca-a12e-4192-acc1-96765d9ae266.htm >> >> -- >> HTH, >> Jasper Smith (SQL Server MVP) >> http://www.sqldbatips.com >> >> >> "jvcoach23" <jvcoac***@discussions.microsoft.com> wrote in message >> news:BF7EBEE6-4B5A-4AD3-98AF-ECBCFBC716B6@microsoft.com... >> > I've been doing some time test between Sql 2000 sp3a and Sql 2005. >> > >> > >> > >> > Test Database Server = Server 1 >> > >> > It's a dual 1800 AMD, 2 Gig or RAM >> > >> > Sql 2000 on the default instance >> > >> > Sql 2005 running on another instance >> > >> > Not running them both at the same time >> > >> > Both have a max memory setting of 1800 Mb >> > >> > >> > >> > >> > >> > I'm using a trc file to do the load testing through profiler. The trc >> > was >> > created using Sql 2000 profiler going against a database I'll call >> > Db2000. >> > >> > >> > >> > I've done a timed test of running through the entire trc file. Sql >> > 2000 >> > is >> > just slightly slower than 2005 (I did a reindex, stats, recompile) >> > >> > >> > >> > Ok.. here is my issue that I'm trying to figure out. When I run this >> > trc >> > file through sql 2005 profiler on client Test1 hitting the database on >> > Server1 and recording the results to Server 2 (I'm only grabbing >> > statements >> > with a duration greater than 5), I'm getting sql statements that have a >> > duration of over 3 million. I run the that same sql statement in >> > Management >> > Studio and it takes 262. >> > >> > >> > >> > So I'm trying to figure out why profiler is seeing the duration as over >> > 3 >> > million milliseconds. The trace file to run in total is never more >> > than >> > 15 >> > minutes total.. so for 1 statement to say it's taking just over 50 >> > minutes.. >> > I just doesn't make sense that it's right. >> > >> > >> > >> > Anyone seen this. >> > >> > >> > >> > >> > >> > -- >> > Shannon Ramirez >> > Network DBA >> >> >>  |
|||||||||||||||||||||||
Other interesting topics