Tracking SQL Performance

[MSIsam]

Is there a way to pull the SQL performance stats from the cashe files in an automated fashion. We would like to pull the time duration for each pass of SQL and store to a txt or xls file. Has anyone done something like this?

 

[entaroadun]

I wrote a utility using the IServer API with VBA in Access 97 that does something very similar to this. It executes a list of reports, then grabs the times for each pass for each.

I'm not sure if the cache file actually stores the performance at the SQL pass level though.

What do you need?

 

[MSIsam]

That seems to be exactly what we need. We would like to have a way to see how long each pass of SQL took each time the reports are being run. For example, we would like the means to answer the following question:
Pass87 of the sales report took 45 minutes this week, how long did it take in the weeks prior to that?

 

[chael]

What about enterprise manager? haven't checked in a while but if you do detail statistics I think EM can give you some of this???

 

[entaroadun]

Yes, if you have detailed statistics, the SQL pass times are recorded. Actually, the entire SQL block is recorded with pass times embedded. I don't know if EM actually ETLs it in, but if you don't mind digging into the stats tables, you can get it.