Query statistics result hourly

Question

Query statistics result hourly

jack_hc

Old Hand

Points: 344
More actions
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

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

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

krayknot SSCertifiable Points: 7998 More actions · Answer 1

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

stephen.christie SSC Eights! Points: 895 More actions · Answer 2

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

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"

jack_hc Old Hand Points: 344 More actions · Answer 4

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

jack_hc Old Hand Points: 344 More actions · Answer 5

[/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.

jack_hc Old Hand Points: 344 More actions · Answer 6

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.