Real-Time Tracking of Tempdb Utilization Through Reporting Services

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    Comments posted to this topic are about the item Real-Time Tracking of Tempdb Utilization Through Reporting Services

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    Unfortunately, some of the figures are not clear enough. I will make sure I make them larger for the next article. 🙂

    Please have a look at the supp. material, and, hopefully, that will make the discussion and figures clearer.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Rick Romack

    Ten Centuries

    Points: 1232

    Great article and I can already see many uses. One thing I did find is that the tSQL scripts to install the jobs assumes that the SA account has not been renamed. On All our servers we rename that account even though we use Windows Only Authentication. I simply edited the script and changed the SA account to the account we use.

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    Rick Romack (3/16/2009)


    Great article and I can already see many uses. One thing I did find is that the tSQL scripts to install the jobs assumes that the SA account has not been renamed. On All our servers we rename that account even though we use Windows Only Authentication. I simply edited the script and changed the SA account to the account we use.

    Thank you and good point.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Misha_SQL

    SSCertifiable

    Points: 5388

    Thank you for the article and detailed solution. I had no idea that RS reports could be made so interactive using "Actions". Learning something every day!

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    mishaluba (3/21/2009)


    Thank you for the article and detailed solution. I had no idea that RS reports could be made so interactive using "Actions". Learning something every day!

    Thanks! Have a look at the new sql-2008 feature of performance data collection and the reports that ship with it. It will give you an even better idea on what is possible with Reporting Services.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • scotta_miller

    SSC Journeyman

    Points: 83

    Thanks, for a Great article. I am trying to implement this on a test server and the report shows no data at all (Except the server name). When I run the stored procs by them selves I get data back. Any idea why the report is blank?

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    scotta_miller (3/23/2009)


    Thanks, for a Great article. I am trying to implement this on a test server and the report shows no data at all (Except the server name). When I run the stored procs by them selves I get data back. Any idea why the report is blank?

    Thank you!

    Are you running it on SQL-2008 Reporting Services? If yes, pls have a look at the bottom of the article (last figure), where I'm discussing a similar issue I was having when I migrated the app from SQL-2005. If you are running it on SQL-2005 SSRS, I will need more info to troubleshoot on.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • scotta_miller

    SSC Journeyman

    Points: 83

    Reporting Services 2005 and all servers are SQL 2005.

    I followed the doc included in the zip file. In reporting services The servernames Dataset returns the correct server name, I checked and the default servername is setup in the report parameters. I had to mod the Credentials setting to use windows authentication for the dymanic connection. I have tried setting all the datasets to use the AppDB.rds and statically assign the single server and still the report is blank. If I copy the Stored procs from the data tab and run them in SQL they return data .

    EXEC [dbo].[rpt_Tempdb_Occurrences_LowestNumberUnallocatedExtentPages] @Top = 20 --- Returns 16 records

    EXEC [dbo].[rpt_Tempdb_Occurrences_HighestNumberVersionStorePages] @Top = 20 --- Returns 20 records

    EXEC [dbo].[rpt_Tempdb_Occurrences_HighestNumberMixedExtentPages] @Top = 20 --- Returns 20 records

    I have created the Utility DB and I put the TempdbSpaceUsage_ServerNames table inside that DB, I have ran all the scripts to create the table and stored procs along with the JOB Script. I am not sure what I did wrong.

    Please let me know what additional information you need???

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    scotta_miller (3/23/2009)


    Reporting Services 2005 and all servers are SQL 2005.

    I followed the doc included in the zip file. In reporting services The servernames Dataset returns the correct server name, I checked and the default servername is setup in the report parameters. I had to mod the Credentials setting to use windows authentication for the dymanic connection. I have tried setting all the datasets to use the AppDB.rds and statically assign the single server and still the report is blank. If I copy the Stored procs from the data tab and run them in SQL they return data .

    EXEC [dbo].[rpt_Tempdb_Occurrences_LowestNumberUnallocatedExtentPages] @Top = 20 --- Returns 16 records

    EXEC [dbo].[rpt_Tempdb_Occurrences_HighestNumberVersionStorePages] @Top = 20 --- Returns 20 records

    EXEC [dbo].[rpt_Tempdb_Occurrences_HighestNumberMixedExtentPages] @Top = 20 --- Returns 20 records

    I have created the Utility DB and I put the TempdbSpaceUsage_ServerNames table inside that DB, I have ran all the scripts to create the table and stored procs along with the JOB Script. I am not sure what I did wrong.

    Please let me know what additional information you need???

    Hmm, I'm not sure what's wrong. I will have a look at the supp material I posted and retrace all the steps mentioned in the readme doc. I may have missed something.

    Can you check whether the visible property is set to true for the chart?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • scotta_miller

    SSC Journeyman

    Points: 83

    The chart visibility is based on the following expression: =iif(Parameters!chartFlag.Value > 0, false, true) --- I tried setting the hidden property to false and got the same result.

    I double checked the DB and there are two table with no data:

    ExecRequests

    TempdbSpaceUsage_Mean_StdDev

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    scotta_miller (3/23/2009)


    The chart visibility is based on the following expression: =iif(Parameters!chartFlag.Value > 0, false, true) --- I tried setting the hidden property to false and got the same result.

    I double checked the DB and there are two table with no data:

    ExecRequests

    TempdbSpaceUsage_Mean_StdDev

    I think I know what may be wrong. Pls start the job titled "DBA - Tempdb Usage - Calculate Stats, Prune Tables". This job must run at least once before you start collecting data. I need to make sure this step is added to the SuppMaterial readme doc.

    I hope this solves the problem, if not, pls let me know.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • scotta_miller

    SSC Journeyman

    Points: 83

    I manually ran the job and then checked the log file and the job has been running every hour since I created it last Friday. Same thing with the Monitor job - it has been running every minute since last Friday.

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    scotta_miller (3/23/2009)


    The chart visibility is based on the following expression: =iif(Parameters!chartFlag.Value > 0, false, true) --- I tried setting the hidden property to false and got the same result.

    I double checked the DB and there are two table with no data:

    ExecRequests

    TempdbSpaceUsage_Mean_StdDev

    The fact that these 2 tables are empty just means that there have been no major peeks in the number of version-store/mixed-extent pages during this time. I forgot to ask you, are you having this problem in BIDS or IE or both?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • scotta_miller

    SSC Journeyman

    Points: 83

    Both, it is blank locally and on the report server.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply