SQL Server Performance

reapplying trace

Discussion in 'Performance Tuning for DBAs' started by ravilobo, May 13, 2004.

  1. ravilobo New Member

    New modules are being added to my DB server. I need to find the affect of these modules on the DB server. I don't want to run a continiuos trace
    on the server every time i add a new module.

    Following are my thoughts to implement this.

    1>Capture the trace ONCE on production server.
    2>Reapply the trace on a STAND BY SERVER.

    Following are my doubts?
    a>Is it possible to reapply the trace on a second server? If YES will this trace create the same load (same environment)on the second server?
    b>Can i increase the load from the trace, like in some testing tools where if the trace gives the connections 10, user can manually increase the
    connections to 20 just to find out what will be the performance hit if the no of users double?

    c>Is there a better way?




    I think, therefore I am
  2. Luis Martin Moderator

    My suggestion is:
    Capture trace on production server, after trace backup and restore database on Stand by server.
    Find on trace long queries or sp, try to optimize those queries.
    I haven't good results with reapply trace.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. ravilobo New Member

    Thank you.
    Is it possible to re-apply trace on a different server?

    I think, therefore I am
  4. Luis Martin Moderator

    Yes, it is, but what I mean is: never was usefull to me.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  5. gaurav_bindlish New Member

    I think it is possible to do the same.

    I would rather recommend creating a SQL Script as a benchmark for the workload and then use it everytime the data model changes happened.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  6. ravilobo New Member

    Ok. Thank you.

    I think, therefore I am
  7. ravilobo New Member

    quote:
    I would rather recommend creating a SQL Script as a benchmark for the workload and then use it everytime the data model changes happened.

    Man...thats what i am looking for!!!
    How to create a SQL script for the workload?

    I think, therefore I am
  8. Luis Martin Moderator

    In profiler, save as: Query Script.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  9. ravilobo New Member

    Thats great. What i understand is that i can save the trace as a sql file.

    Thats good news. Thanks a lot.

    I think you are on line. Thats why you are replying so soon. Which part of the world you are in? We both are in same time zone. Its like chatting...

    I think, therefore I am
  10. gaurav_bindlish New Member

    It would be nice to have a generic script written manually. You can use the Profiler data for the same but having a nicely written script which covers almost (all the queries in the system) * (Times the query is run) will give a more realistic solution.

    Also if you want to simulate multiple users, write a small VB program to rum multiple threads and call the script from individual threads.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  11. derrickleggett New Member

    We run a couple traces. One traces anything that runs over 1.5 seconds. The other one traces anything with more than 2000 reads. We just have them dumped to tables in SQL Server called LongRunning and HeavyReads. We then produce TOP 25 reports off of it and monitor with these scripts. They are pretty rough, but I've never saw a reason to spend the time updating them. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />-- select LoginName, HostName, count(*)<br />-- from UserConnections<br />-- group by LoginName, HostName<br /><br />DECLARE <br />@dteStartTime DATETIME,<br />@dteEndTime DATETIME<br /><br />SELECT<br />@dteStartTime = (<br />SELECT CAST(LEFT(GETDATE(),11) AS VARCHAR(55)) + ' 08:00:00.000'),<br />--DATEADD(MI,-5,GETDATE())),<br />--SELECT CAST(LEFT(DATEADD(DD,-8,GETDATE()),11) AS VARCHAR(55))),<br />--SELECT CAST(LEFT(DATEADD(DD,-1,GETDATE()),11) AS VARCHAR(55)) + ' 08:00:00.000'),<br />@dteEndTime = (<br />SELECT GETDATE())<br />--SELECT CAST(LEFT(DATEADD(DD,-1,GETDATE()),11) AS VARCHAR(55)) + ' 20:00:00.000')<br />--SELECT CAST(LEFT(CAST('02/07/04' AS DATETIME),11) AS VARCHAR(55)) + ' 23:59:59.000')<br /><br />SELECT @dteStartTime, @dteEndTime<br /><br />-- select * from LongRunning where StartTime &gt;= @dteStartTime <br />-- select * from HeavyReads where StartTime &gt;= @dteStartTime<br /><br />-- SELECT * <br />-- FROM LongRunning <br />-- WHERE <br />-- DatabaseID = (<br />-- SELECT dbid FROM MKCSSQL01.master.dbo.sysdatabases WHERE name = 'los')<br />-- AND StartTime &gt;= @dteStartTime<br /><br />DECLARE @databases TABLE(<br />dbid INT PRIMARY KEY,<br />name VARCHAR(255))<br /><br />INSERT @databases(<br />dbid,<br />name)<br /><br />SELECT<br />dbid,<br />name<br />FROM<br />MKCSSQL01.master.dbo.sysdatabases<br /><br />IF (SELECT name FROM sysindexes WHERE name = 'idx_LongRunning_DatabaseID') IS NULL<br />BEGIN<br />CREATE INDEX idx_LongRunning_DatabaseID ON LongRunning(DatabaseID)<br />END<br /><br />IF (SELECT name FROM sysindexes WHERE name = 'idx_LongRunning_StartTime') IS NULL<br />BEGIN<br />CREATE INDEX idx_LongRunning_StartTime ON LongRunning(StartTime)<br />END<br /><br />IF (SELECT name FROM sysindexes WHERE name = 'idx_HeavyReads_DatabaseID') IS NULL<br />BEGIN<br />CREATE INDEX idx_HeavyReads_DatabaseID ON HeavyReads(DatabaseID)<br />END<br /><br />IF (SELECT name FROM sysindexes WHERE name = 'idx_HeavyReads_StartTime') IS NULL<br />BEGIN<br />CREATE INDEX idx_HeavyReads_StartTime ON HeavyReads(StartTime)<br />END<br /><br />SELECT --TOP 26<br />cc.DatabaseName, --1<br />RTRIM(LTRIM(cc.ColumnName)) AS ColumnName, --2<br />COUNT(cc.ColumnName) AS Counter, --3<br />AVG(cc.Duration) AS AvgDuration, --4<br />MAX(cc.EndTime) AS LastLongRunningTime, --5<br /> AVG(cc.CPU) AS AvgCPU, --6<br />AVG(cc.Reads) AS AvgReads, --7<br />AVG(cc.Writes) AS AvgWrites, --8<br />SUM(cc.CPU) AS TotalCPU, --9<br />SUM(cc.Reads) AS TotalReads, --10<br />SUM(cc.Writes) AS TotalWrites, --11<br /> SUM(cc.Reads) + SUM(cc.Writes) AS TotalReadsWrites, --12<br />MIN(cc.EndTime) AS FirstRunTime, --13<br />MAX(cc.EndTime) AS LastRunTime --14<br />FROM (<br />SELECT<br />d.name AS DatabaseName,<br />CASE<br />WHEN UPPER((RTRIM(LTRIM(LEFT((CAST(lr.TextData AS VARCHAR(2500))),4))))) = 'EXEC' <br />THEN RTRIM(LTRIM(LEFT((RTRIM(LTRIM(RIGHT((RTRIM(LTRIM((CAST(lr.TextData AS VARCHAR(2500)))))),LEN((RTRIM(LTRIM((CAST(lr.TextData AS VARCHAR(2500)))))))-(CHARINDEX(' ',lr.TextData)))))),(CHARINDEX(' ',(RTRIM(LTRIM(RIGHT((RTRIM(LTRIM((CAST(lr.TextData AS VARCHAR(2500)))))),LEN((RTRIM(LTRIM((CAST(lr.TextData AS VARCHAR(2500)))))))-(CHARINDEX(' ',lr.TextData)))))) + ' ')))))<br />ELSE RTRIM(LTRIM(LEFT((CAST(TextData AS VARCHAR(2500))),(CHARINDEX(' ',(CAST(TextData AS VARCHAR(2500))))))))<br />END AS ColumnName,<br />lr.TextData,<br />lr.Duration,<br />lr.EndTime,<br />lr.CPU,<br />lr.Reads,<br />lr.Writes<br />FROM <br />LongRunning lr WITH(NOLOCK)<br />INNER JOIN @databases d ON lr.DatabaseID = d.dbid <br />WHERE<br />lr.StartTime BETWEEN @dteStartTime AND @dteEndTime<br />) cc<br />WHERE <br />cc.ColumnName &lt;&gt; ''<br />AND UPPER(RTRIM(LTRIM(cc.ColumnName)))NOT IN(<br />--'SP_EXECUTESQL',<br />'SP_DATATYPE_INFO',<br />'MSDB.DBO.SP_SQLAGENT_LOG_JOBHISTORY','SP_SPROC_COLUMNS','NON-INTERFACE','BACKUP','DELETE','INSERT',<br />'UPDATE','[NHM]..SP_PROCEDURE_PARAMS_ROWSET', 'CREATE','--','--DECLARE','PRINT','ALTER','DROP','--POPULATE','SELECT',<br />'DBCC')<br />--AND UPPER(LEFT((ColumnName),7)) NOT LIKE 'DECLARE'<br />--AND UPPER(LEFT(RTRIM(LTRIM((ColumnName))),6)) NOT LIKE 'SELECT'<br />--AND UPPER(cc.ColumnName) LIKE '%RPTPIPELINEBYAE%'<br />GROUP BY cc.DatabaseName, cc.ColumnName<br />--HAVING COUNT(*)&gt;5<br />ORDER BY 3 DESC<br /><br />SELECT --TOP 26<br />cc.DatabaseName, --1<br />RTRIM(LTRIM(cc.ColumnName)) AS ColumnName, --2<br />COUNT(cc.ColumnName) AS Counter, --3<br />AVG(cc.Duration) AS AvgDuration, --4<br />MAX(cc.EndTime) AS LastLongRunningTime, --5<br /> AVG(cc.CPU) AS AvgCPU, --6<br />AVG(cc.Reads) AS AvgReads, --7<br />AVG(cc.Writes) AS AvgWrites, --8<br />SUM(cc.CPU) AS TotalCPU, --9<br />SUM(cc.Reads) AS TotalReads, --10<br />SUM(cc.Writes) AS TotalWrites, --11<br />SUM(cc.Reads) + SUM(cc.Writes) AS TotalReadsWrites, --12<br />MIN(cc.EndTime) AS FirstRunTime, --13<br />MAX(cc.EndTime) AS LastRunTime --14<br />FROM (<br />SELECT<br />d.name AS DatabaseName,<br />CASE<br />WHEN UPPER((RTRIM(LTRIM(LEFT((CAST(hr.TextData AS VARCHAR(2500))),4))))) = 'EXEC' <br />THEN RTRIM(LTRIM(LEFT((RTRIM(LTRIM(RIGHT((RTRIM(LTRIM((CAST(hr.TextData AS VARCHAR(2500)))))),LEN((RTRIM(LTRIM((CAST(hr.TextData AS VARCHAR(2500)))))))-(CHARINDEX(' ',hr.TextData)))))),(CHARINDEX(' ',(RTRIM(LTRIM(RIGHT((RTRIM(LTRIM((CAST(hr.TextData AS VARCHAR(2500)))))),LEN((RTRIM(LTRIM((CAST(hr.TextData AS VARCHAR(2500)))))))-(CHARINDEX(' ',hr.TextData)))))) + ' ')))))<br />ELSE RTRIM(LTRIM(LEFT((CAST(hr.TextData AS VARCHAR(2500))),(CHARINDEX(' ',(CAST(hr.TextData AS VARCHAR(2500))))))))<br />END AS ColumnName,<br />hr.TextData,<br />hr.Duration,<br />hr.EndTime,<br />hr.CPU,<br />hr.Reads,<br />hr.Writes<br />FROM <br />HeavyReads hr WITH(NOLOCK)<br />INNER JOIN @databases d ON hr.DatabaseID = d.dbid<br />WHERE <br />hr.StartTime BETWEEN @dteStartTime AND @dteEndTime<br />) cc<br />WHERE <br />cc.ColumnName &lt;&gt; ''<br />AND UPPER(RTRIM(LTRIM(cc.ColumnName)))NOT IN(<br />--'SP_EXECUTESQL',<br />'SP_DATATYPE_INFO',<br />'MSDB.DBO.SP_SQLAGENT_LOG_JOBHISTORY','SP_SPROC_COLUMNS','NON-INTERFACE','BACKUP','DELETE','INSERT',<br />'UPDATE','[NHM]..SP_PROCEDURE_PARAMS_ROWSET', 'CREATE','--','--DECLARE','PRINT','ALTER','DROP','--POPULATE','SELECT')<br />--AND UPPER(LEFT((ColumnName),7)) NOT LIKE 'DECLARE'<br />--AND UPPER(LEFT(RTRIM(LTRIM((ColumnName))),6)) NOT LIKE 'SELECT'<br />--AND UPPER(cc.ColumnName) LIKE '%RPTPIPELINEBYAE%'<br />GROUP BY cc.DatabaseName, cc.ColumnName<br />ORDER BY 12 DESC<br /><br />--Get summary information.<br />DECLARE <br />@reads INT,<br />@date DATETIME<br /><br />SELECT @date = CAST(DATEPART(MM,GETDATE()) AS VARCHAR(5)) + '/' +<br />CAST(DATEPART(DD,GETDATE()) AS VARCHAR(5)) + '/' + <br />CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR(5)) <br /><br />SELECT @reads = SUM(Reads) <br />FROM HeavyReads (NOLOCK)<br />WHERE <br />DATEPART(hh,StartTime) BETWEEN 7 AND 18<br />AND StartTime BETWEEN @date AND @date + 1<br /><br />SELECT<br />MIN(StartTime) StartTime ,<br />MAX(StartTime) MaxTime<br />FROM<br />HeavyReads (NOLOCK)<br /><br />SELECT<br />SUBSTRING(TextData,1,1<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />SUM(Reads), <br />CONVERT(DECIMAL(3,1),(CONVERT(FLOAT,SUM(Reads)) / @reads) * 100) AS Pct,<br />AVG(Reads) AvgReads, <br />COUNT(Reads) CntReads<br />FROM<br />HeavyReads (NOLOCK)<br />WHERE<br />DATEPART(hh,StartTime) BETWEEN 7 AND 18<br />AND StartTime BETWEEN @date AND @date + 1<br />GROUP BY<br />SUBSTRING(TextData,1,1<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />HAVING<br />SUM(Reads) &gt; 10000<br />ORDER BY SUM(Reads) DESC<br />COMPUTE<br />SUM(SUM(Reads)),<br />SUM(CONVERT(DECIMAL(3,1),(CONVERT(FLOAT,SUM(Reads)) / @reads) * 100))<br /><br /> <br /><br /><br /> <br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  12. derrickleggett New Member

    I can also send the profiler trace templates we run if anyone wants them. We have one to track DDL changes, a security audit, and the two above. When you run a trace or profiler, we have found it more efficient and secure to run the trace or profile from another machine. The machine can have it dump directly to SQL Server if it has it installed. We have our "management station" do this.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  13. Luis Martin Moderator

    "I think you are on line. Thats why you are replying so soon. Which part of the world you are in? We both are in same time zone. Its like chatting..."

    Some times yes, some times no. About world you can see:
    http://www.sql-server-performance.com/forum/members.asp



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  14. ravilobo New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />When life gives you a lemon, fire the DBA.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Hey lemon guy....! I have seen you on one more site. I am sure you don't know what that quote means....<img src='/community/emoticons/emotion-5.gif' alt=';-)' /> <br /><br />Thanks a lot for your information on trace.<br /><br />I think, therefore I am
  15. ravilobo New Member

    If i execute the trace on a stand by server, will it reciprocate the number of CONNECETD USRES ALSO? else how to implement this?

    I think, therefore I am
  16. ravilobo New Member

    If i execute the trace on a stand by server, will it reciprocate the number of CONNECETD USRES ALSO? else how to implement this?

    I think, therefore I am
  17. derrickleggett New Member

    Hey lemon guy....! I have seen you on one more site. I am sure you don't know what that quote means....<img src='/community/emoticons/emotion-5.gif' alt=';-)' /> <br /><br />I'm sure you can't be sure of that. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />To replicate the number of executed users, you have to set up a test environment or application that will open up connections to a certain level. Executing a trace will not do this.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  18. ravilobo New Member

    Does anyone has load generating scripts for SQL2K?

    Any leads.....

    Thanks a lot.

    I think, therefore I am
  19. satya Moderator

  20. ravilobo New Member

    Read the information.
    The link talks about the book and back office kit. I don't have both.


    Are there any pregenerated scripts?
    Any help will be greatly appreciated.



    I think, therefore I am
  21. satya Moderator

  22. ravilobo New Member

    Thank you for the quick response.
    This article talks about analysing the trace. Finding the bad queries etc....

    I am looking for a script which can generate the laod on the stand by server. (So that the enicronment is similar to production server)



    I think, therefore I am
  23. satya Moderator

  24. ravilobo New Member

    Few more questions...

    1>I have captured the trace on one DB, is it possible to execute the same trace on diffrent DB?
    2>My trace has a insert statement, when i REPLAY the trace the records are not getting inserted in the DB?

    Any leads.....




    I think, therefore I am
  25. gaurav_bindlish New Member

    I think it is possible as long as the database objects are the same. However not having the same base data on which the queries were intially run would mean most of the operations would fail.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  26. satya Moderator

    Please confirm the version of SQL & Service pack level?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  27. ravilobo New Member

    Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
    Dec 17 2002 14:22:05
    Copyright (c) 1988-2003 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

    I think, therefore I am
  28. zsamz New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by derrickleggett</i><br /><br />We run a couple traces. One traces anything that runs over 1.5 seconds. The other one traces anything with more than 2000 reads. We just have them dumped to tables in SQL Server called LongRunning and HeavyReads. We then produce TOP 25 reports off of it and monitor with these scripts. They are pretty rough, but I've never saw a reason to spend the time updating them. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />-- select LoginName, HostName, count(*)<br />-- from UserConnections<br />-- group by LoginName, HostName<br /><br />DECLARE <br />@dteStartTime DATETIME,<br />@dteEndTime DATETIME<br /><br />SELECT<br />@dteStartTime = (<br />SELECT CAST(LEFT(GETDATE(),11) AS VARCHAR(55)) + ' 08:00:00.000'),<br />--DATEADD(MI,-5,GETDATE())),<br />--SELECT CAST(LEFT(DATEADD(DD,-8,GETDATE()),11) AS VARCHAR(55))),<br />--SELECT CAST(LEFT(DATEADD(DD,-1,GETDATE()),11) AS VARCHAR(55)) + ' 08:00:00.000'),<br />@dteEndTime = (<br />SELECT GETDATE())<br />--SELECT CAST(LEFT(DATEADD(DD,-1,GETDATE()),11) AS VARCHAR(55)) + ' 20:00:00.000')<br />--SELECT CAST(LEFT(CAST('02/07/04' AS DATETIME),11) AS VARCHAR(55)) + ' 23:59:59.000')<br /><br />SELECT @dteStartTime, @dteEndTime<br /><br />-- select * from LongRunning where StartTime &gt;= @dteStartTime <br />-- select * from HeavyReads where StartTime &gt;= @dteStartTime<br /><br />-- SELECT * <br />-- FROM LongRunning <br />-- WHERE <br />-- DatabaseID = (<br />-- SELECT dbid FROM MKCSSQL01.master.dbo.sysdatabases WHERE name = 'los')<br />-- AND StartTime &gt;= @dteStartTime<br /><br />DECLARE @databases TABLE(<br />dbid INT PRIMARY KEY,<br />name VARCHAR(255))<br /><br />INSERT @databases(<br />dbid,<br />name)<br /><br />SELECT<br />dbid,<br />name<br />FROM<br />MKCSSQL01.master.dbo.sysdatabases<br /><br />IF (SELECT name FROM sysindexes WHERE name = 'idx_LongRunning_DatabaseID') IS NULL<br />BEGIN<br />CREATE INDEX idx_LongRunning_DatabaseID ON LongRunning(DatabaseID)<br />END<br /><br />IF (SELECT name FROM sysindexes WHERE name = 'idx_LongRunning_StartTime') IS NULL<br />BEGIN<br />CREATE INDEX idx_LongRunning_StartTime ON LongRunning(StartTime)<br />END<br /><br />IF (SELECT name FROM sysindexes WHERE name = 'idx_HeavyReads_DatabaseID') IS NULL<br />BEGIN<br />CREATE INDEX idx_HeavyReads_DatabaseID ON HeavyReads(DatabaseID)<br />END<br /><br />IF (SELECT name FROM sysindexes WHERE name = 'idx_HeavyReads_StartTime') IS NULL<br />BEGIN<br />CREATE INDEX idx_HeavyReads_StartTime ON HeavyReads(StartTime)<br />END<br /><br />SELECT --TOP 26<br />cc.DatabaseName, --1<br />RTRIM(LTRIM(cc.ColumnName)) AS ColumnName, --2<br />COUNT(cc.ColumnName) AS Counter, --3<br />AVG(cc.Duration) AS AvgDuration, --4<br />MAX(cc.EndTime) AS LastLongRunningTime, --5<br /> AVG(cc.CPU) AS AvgCPU, --6<br />AVG(cc.Reads) AS AvgReads, --7<br />AVG(cc.Writes) AS AvgWrites, --8<br />SUM(cc.CPU) AS TotalCPU, --9<br />SUM(cc.Reads) AS TotalReads, --10<br />SUM(cc.Writes) AS TotalWrites, --11<br /> SUM(cc.Reads) + SUM(cc.Writes) AS TotalReadsWrites, --12<br />MIN(cc.EndTime) AS FirstRunTime, --13<br />MAX(cc.EndTime) AS LastRunTime --14<br />FROM (<br />SELECT<br />d.name AS DatabaseName,<br />CASE<br />WHEN UPPER((RTRIM(LTRIM(LEFT((CAST(lr.TextData AS VARCHAR(2500))),4))))) = 'EXEC' <br />THEN RTRIM(LTRIM(LEFT((RTRIM(LTRIM(RIGHT((RTRIM(LTRIM((CAST(lr.TextData AS VARCHAR(2500)))))),LEN((RTRIM(LTRIM((CAST(lr.TextData AS VARCHAR(2500)))))))-(CHARINDEX(' ',lr.TextData)))))),(CHARINDEX(' ',(RTRIM(LTRIM(RIGHT((RTRIM(LTRIM((CAST(lr.TextData AS VARCHAR(2500)))))),LEN((RTRIM(LTRIM((CAST(lr.TextData AS VARCHAR(2500)))))))-(CHARINDEX(' ',lr.TextData)))))) + ' ')))))<br />ELSE RTRIM(LTRIM(LEFT((CAST(TextData AS VARCHAR(2500))),(CHARINDEX(' ',(CAST(TextData AS VARCHAR(2500))))))))<br />END AS ColumnName,<br />lr.TextData,<br />lr.Duration,<br />lr.EndTime,<br />lr.CPU,<br />lr.Reads,<br />lr.Writes<br />FROM <br />LongRunning lr WITH(NOLOCK)<br />INNER JOIN @databases d ON lr.DatabaseID = d.dbid <br />WHERE<br />lr.StartTime BETWEEN @dteStartTime AND @dteEndTime<br />) cc<br />WHERE <br />cc.ColumnName &lt;&gt; ''<br />AND UPPER(RTRIM(LTRIM(cc.ColumnName)))NOT IN(<br />--'SP_EXECUTESQL',<br />'SP_DATATYPE_INFO',<br />'MSDB.DBO.SP_SQLAGENT_LOG_JOBHISTORY','SP_SPROC_COLUMNS','NON-INTERFACE','BACKUP','DELETE','INSERT',<br />'UPDATE','[NHM]..SP_PROCEDURE_PARAMS_ROWSET', 'CREATE','--','--DECLARE','PRINT','ALTER','DROP','--POPULATE','SELECT',<br />'DBCC')<br />--AND UPPER(LEFT((ColumnName),7)) NOT LIKE 'DECLARE'<br />--AND UPPER(LEFT(RTRIM(LTRIM((ColumnName))),6)) NOT LIKE 'SELECT'<br />--AND UPPER(cc.ColumnName) LIKE '%RPTPIPELINEBYAE%'<br />GROUP BY cc.DatabaseName, cc.ColumnName<br />--HAVING COUNT(*)&gt;5<br />ORDER BY 3 DESC<br /><br />SELECT --TOP 26<br />cc.DatabaseName, --1<br />RTRIM(LTRIM(cc.ColumnName)) AS ColumnName, --2<br />COUNT(cc.ColumnName) AS Counter, --3<br />AVG(cc.Duration) AS AvgDuration, --4<br />MAX(cc.EndTime) AS LastLongRunningTime, --5<br /> AVG(cc.CPU) AS AvgCPU, --6<br />AVG(cc.Reads) AS AvgReads, --7<br />AVG(cc.Writes) AS AvgWrites, --8<br />SUM(cc.CPU) AS TotalCPU, --9<br />SUM(cc.Reads) AS TotalReads, --10<br />SUM(cc.Writes) AS TotalWrites, --11<br />SUM(cc.Reads) + SUM(cc.Writes) AS TotalReadsWrites, --12<br />MIN(cc.EndTime) AS FirstRunTime, --13<br />MAX(cc.EndTime) AS LastRunTime --14<br />FROM (<br />SELECT<br />d.name AS DatabaseName,<br />CASE<br />WHEN UPPER((RTRIM(LTRIM(LEFT((CAST(hr.TextData AS VARCHAR(2500))),4))))) = 'EXEC' <br />THEN RTRIM(LTRIM(LEFT((RTRIM(LTRIM(RIGHT((RTRIM(LTRIM((CAST(hr.TextData AS VARCHAR(2500)))))),LEN((RTRIM(LTRIM((CAST(hr.TextData AS VARCHAR(2500)))))))-(CHARINDEX(' ',hr.TextData)))))),(CHARINDEX(' ',(RTRIM(LTRIM(RIGHT((RTRIM(LTRIM((CAST(hr.TextData AS VARCHAR(2500)))))),LEN((RTRIM(LTRIM((CAST(hr.TextData AS VARCHAR(2500)))))))-(CHARINDEX(' ',hr.TextData)))))) + ' ')))))<br />ELSE RTRIM(LTRIM(LEFT((CAST(hr.TextData AS VARCHAR(2500))),(CHARINDEX(' ',(CAST(hr.TextData AS VARCHAR(2500))))))))<br />END AS ColumnName,<br />hr.TextData,<br />hr.Duration,<br />hr.EndTime,<br />hr.CPU,<br />hr.Reads,<br />hr.Writes<br />FROM <br />HeavyReads hr WITH(NOLOCK)<br />INNER JOIN @databases d ON hr.DatabaseID = d.dbid<br />WHERE <br />hr.StartTime BETWEEN @dteStartTime AND @dteEndTime<br />) cc<br />WHERE <br />cc.ColumnName &lt;&gt; ''<br />AND UPPER(RTRIM(LTRIM(cc.ColumnName)))NOT IN(<br />--'SP_EXECUTESQL',<br />'SP_DATATYPE_INFO',<br />'MSDB.DBO.SP_SQLAGENT_LOG_JOBHISTORY','SP_SPROC_COLUMNS','NON-INTERFACE','BACKUP','DELETE','INSERT',<br />'UPDATE','[NHM]..SP_PROCEDURE_PARAMS_ROWSET', 'CREATE','--','--DECLARE','PRINT','ALTER','DROP','--POPULATE','SELECT')<br />--AND UPPER(LEFT((ColumnName),7)) NOT LIKE 'DECLARE'<br />--AND UPPER(LEFT(RTRIM(LTRIM((ColumnName))),6)) NOT LIKE 'SELECT'<br />--AND UPPER(cc.ColumnName) LIKE '%RPTPIPELINEBYAE%'<br />GROUP BY cc.DatabaseName, cc.ColumnName<br />ORDER BY 12 DESC<br /><br />--Get summary information.<br />DECLARE <br />@reads INT,<br />@date DATETIME<br /><br />SELECT @date = CAST(DATEPART(MM,GETDATE()) AS VARCHAR(5)) + '/' +<br />CAST(DATEPART(DD,GETDATE()) AS VARCHAR(5)) + '/' + <br />CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR(5)) <br /><br />SELECT @reads = SUM(Reads) <br />FROM HeavyReads (NOLOCK)<br />WHERE <br />DATEPART(hh,StartTime) BETWEEN 7 AND 18<br />AND StartTime BETWEEN @date AND @date + 1<br /><br />SELECT<br />MIN(StartTime) StartTime ,<br />MAX(StartTime) MaxTime<br />FROM<br />HeavyReads (NOLOCK)<br /><br />SELECT<br />SUBSTRING(TextData,1,1<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />SUM(Reads), <br />CONVERT(DECIMAL(3,1),(CONVERT(FLOAT,SUM(Reads)) / @reads) * 100) AS Pct,<br />AVG(Reads) AvgReads, <br />COUNT(Reads) CntReads<br />FROM<br />HeavyReads (NOLOCK)<br />WHERE<br />DATEPART(hh,StartTime) BETWEEN 7 AND 18<br />AND StartTime BETWEEN @date AND @date + 1<br />GROUP BY<br />SUBSTRING(TextData,1,1<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />HAVING<br />SUM(Reads) &gt; 10000<br />ORDER BY SUM(Reads) DESC<br />COMPUTE<br />SUM(SUM(Reads)),<br />SUM(CONVERT(DECIMAL(3,1),(CONVERT(FLOAT,SUM(Reads)) / @reads) * 100))<br /><br /> <br /><br /><br /> <br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

Share This Page