Post reply

Query statistics result hourly

  • February 3, 2009 at 10:18 pm

    #126192

    Hi buddy,

    i have a request from a user to pull out data hourly (9am-5pm) from statistics table sys.dm_os_wait_stats in SQL2005. the output can be either csv or a table. just 1 day data. There are 3 ways i can think of.

    1. create DTS package to export data to result.csv file, and schedule it run hourly, since only one file name can be assigned, so old data will be overwriten by later export, so it will not meet my requirement. i need hourly historical data.

    2. DTS package to export table to a new DB/table, use append option, schedule job run hourly, so i will have all data not overwrite, but there's no timestamp in the records, when i sort, i couldn't easily identify when the data is queried. if it runs 10 times a day, i will have 10 records for each wait_type.

    3. program a sql command to a job running hourly. like "select * into query1 from sys.dm_os_wait_stats, but i don't know how to make the tablename variable, such as query1, query2, query3....,although there's no timestamp in this scenario, but i am able to know the sequence of query result, it will be good enough.

    i am not sure whether i made clear my objective, could someone pls help me, if there's better way to do it, i'd be glad to learn.

    Thanks,

    Jack

  • February 3, 2009 at 10:35 pm

    #937714

    jack_hc (2/3/2009)

    Hi buddy,

    i have a request from a user to pull out data hourly (9am-5pm) from statistics table sys.dm_os_wait_stats in SQL2005. the output can be either csv or a table. just 1 day data. There are 3 ways i can think of.

    1. create DTS package to export data to result.csv file, and schedule it run hourly, since only one file name can be assigned, so old data will be overwriten by later export, so it will not meet my requirement. i need hourly historical data.

    2. DTS package to export table to a new DB/table, use append option, schedule job run hourly, so i will have all data not overwrite, but there's no timestamp in the records, when i sort, i couldn't easily identify when the data is queried. if it runs 10 times a day, i will have 10 records for each wait_type.

    3. program a sql command to a job running hourly. like "select * into query1 from sys.dm_os_wait_stats, but i don't know how to make the tablename variable, such as query1, query2, query3....,although there's no timestamp in this scenario, but i am able to know the sequence of query result, it will be good enough.

    i am not sure whether i made clear my objective, could someone pls help me, if there's better way to do it, i'd be glad to learn.

    Thanks,

    Jack

    There are lots of ways to perform this action , the simplest one i think is:

    create a job and set its schedule as per the required timings, and merge the data each time in the previous set of data.

    and yes you can create table variables and can use them like using other tables with some limitations.

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • February 3, 2009 at 10:41 pm

    #937716

    Hi,

    You can use option 2 where you create a SSIS package and populate a table.

    Create the table with the information you request then add a new column on with entered_datetime, set this with a default value of GETDATE(), this way you should be able to distinguish what records where from when.

    This date time will also help if you create a report to see the information.

    Also try and partition this table so you can drop off historic data easily, and make sure that table will perform when user queries it.

    Hope this helps;)

    Kind Regards

    Stephen

  • February 3, 2009 at 11:16 pm

    #937721

    Yes, use a column with default GETDATE() to group the records according to datetime.

    BTW why do you need this data on a hourly basis.

    "Keep Trying"

  • February 3, 2009 at 11:49 pm

    #937726

    stephen.christie (2/3/2009)

    Hi,

    You can use option 2 where you create a SSIS package and populate a table.

    Create the table with the information you request then add a new column on with entered_datetime, set this with a default value of GETDATE(), this way you should be able to distinguish what records where from when.

    This date time will also help if you create a report to see the information.

    Also try and partition this table so you can drop off historic data easily, and make sure that table will perform when user queries it.

    Hope this helps;)

    Kind Regards

    Stephen

    Hi Stephen,

    How brilliant it is! the output is exactly what i expected.

    this is with least amount of work to archive the goal.

    Thanks for your help Stephen!

    Jack

  • February 3, 2009 at 11:53 pm

    #937727

    [/quote]

    There are lots of ways to perform this action , the simplest one i think is:

    create a job and set its schedule as per the required timings, and merge the data each time in the previous set of data.

    and yes you can create table variables and can use them like using other tables with some limitations.

    [/quote]

    thanks krayknot, i used Stephen's suggestion to finished the job.

  • February 3, 2009 at 11:55 pm

    #937729

    Chirag (2/3/2009)

    Yes, use a column with default GETDATE() to group the records according to datetime.

    BTW why do you need this data on a hourly basis.

    they are expecting to see how the DB server behave from application perspective.

Viewing 7 posts - 1 through 6 (of 6 total)

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