SQL Server Performance

TSQL - Avg disk utalization report from a database.

Discussion in 'ALL SQL SERVER QUESTIONS' started by Arman, Mar 1, 2012.

  1. Arman New Member

    All, I have a date in my table which contains the disk utilization data for each server and for each disk.As i put the sample below. Now i am trying to create a report which shows the AVG disk utilization for each day for each disk.
    I am stuck with this as i am trying to use Group by clause but it is giving me the same records for each repeated row.

    --- Here is my table looks like---

    ServerName Disk Time Utilization TimeDelta
    Test G:\ 2/20/2012 16:22 22 .50722565 600.922
    Test G:\ 2/20/2012 16:32 22 .5072256 600.172
    Test I:\ 2/20/2012 16:42 22. 50722565 599.859
    Test I:\ 2/20/2012 16:52 22 .50722565 600.047
    Test C:\ 2/20/2012 17:02 22. 50722565 599.985
    Test C:\ 2/20/2012 17:12 22. 50722565 600.093
    Test H:\ 2/20/2012 17:22 22. 50722565 600.688
    Test H:\ 2/20/2012 17:32 22 .50722565 600.14
    Test G:\ 2/20/2012 17:42 22 .50722565 600.25

    ---- here is the query i am trying to use but no luck----

    SELECT DISTINCT (DISK),
    AVG(Utilization) ,
    CONVERT(CHAR(10),PollTime,120)AS Date FROM Disk
    WHERE CONVERT(CHAR(10),PollTime,120) = '2012-02-20'
    AND name = 'TEST'
    Group by Disk,
    Utilization ,
    ServerName,
    PollTime
    Order BY Disk desc

    Thank For all help.
    Arman, Mar 1, 2012
    #1

  2. Shehap Moderator

    If you are going to group data by Disk & Data , you have to exclude the other columns from Group by command and get enough by these 2 columns (Disk +Date) exclusively as below:

    SELECT (DISK),

    AVG(Utilization),

    CONVERT(CHAR(10),PollTime,120)ASDateFROMDisk

    WHERECONVERT(CHAR(10),PollTime,120)='2012-02-20'

    AND name ='TEST'

    GroupbyDisk,

    CONVERT(CHAR(10),PollTime,120)

    OrderBYDiskdesc
    Shehap, Mar 2, 2012
    #2

  3. Arman New Member

    Thanks for your reply!

    The problem is that in my table i have data for many servers each with having various drives. So i have to know the server name in the output. Also this report is for whole month so how can i get that ?

    Thanks.
    Arman, Mar 2, 2012
    #3

  4. Shehap Moderator

    We have here 2 tiers either to :

    1- Use the below query to get average utilization for each disk of each server for each month as records results

    SELECTDISk,

    AVG(Utilization),

    month(PollTime)AS [Month] FROMDisk,

    Servername WHERE name ='TEST'

    Groupby ServerName ,Disk, month(PollTime)OrderBYDiskdesc

    2- Use the below pivot query to get average utilization for each disk for each month for a specific set of servers as pivot results like below :

    declare @paramters NVARCHAR (1000)=N'[Server1],[Server2]'

    declare @sql nvarchar (max)=N'

    SELECT DISK , month (PollTime) AS [Month] FROM Disk Pivot (AVG(Utilization) for ServerName in '+@paramters +')AS Pivottable WHERE name = ''TEST'''

    execsp_executesql@SQL

    Where you could easily add any servers to @paramter varibale …..Please work out the last one and let me know your input

    BTW ,pivot queries are much readable if you need to extract some data from DMV & DMF while you are doing your daily jobs as DBA , thereby it is much worthwhile to have use to use them
    Shehap, Mar 2, 2012
    #4

Share This Page