How can I query (using TSQL) the data space used by a table in SQL Server 2005?
This is possible using SQL Serve Management studio. I can right click on table name and check the proeprties. But I want to write a TSQL script to check disk space used by all the individual tables in the database. How can I do that?
EXECUTE sp_spaceused 'MyTable'
|||Thanks Arnie... sp_spaceused is what I was looking for.
sqlI set up a trace, launced the report and ran the SP from with SQL MS, then stopped the trace when the report came back. Instead of requesting a whole year's worth of data (which can take over 10 minute), I used a month. It takes about 15 times longer to run the report via RS than it does from within SQL MS.
When ran via RS, there are over 50x the number of reads.
From trace log:
This is the query ran from within MS:
exec SalesByDivision
@.beginDate='2006-12-31 00:00:00:000',
@.endDate='2007-02-03 00:00:00:000',
@.lyBeginDate='2006-01-01 00:00:00:000',
@.lyEndDate='2006-01-28 00:00:00:000',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 10975 reads, 0 writes, and it took 1001 milliseconds
This is the query ran by the report server:
exec SalesByDivision
@.beginDate=''2006-12-31 00:00:00:000'',
@.endDate=''2007-02-03 00:00:00:000'',
@.lyBeginDate=''2006-01-01 00:00:00:000'',
@.lyEndDate=''2006-01-28 00:00:00:000'',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 601409 reads, 0 writes, and it took 15326 milliseconds
Did you try to just copy the exact text from the profiler? (to ensure that typo's and oversights aren't an issue)
Also, is the amount of system resources available possibly an issue?
Meaning, when running with just SQL MS, you are just using sql, but when you introduce RS, that's another program eating resources as well.
Thanks for the questions.
Yes, I copied the SQL from profiler then ran that in SQL MS.
These results are from a new production box. The system is consistently under load. Most reports that users are running are for very small date ranges and return within an acceptable time. However, reports with longer date ranges (the ones that managers and execs often run) are noticeby slower. Whether I run the SP from SQL MS or if I run a report, I assume the same resources are being used by RS.
|||There is only one version of the stored proc. For kicks I tried updating a report to include dbo. in front of the SP name. There was no noticable difference in load time.|||but add in t-sql commands to your trace. Do you know how to do this?|||WITH RECOMPILE
Another one of our developers found a forum where someone had posted the same issue. Their suggestion was to add WITH RECOMPILE to our stored proc. After doing this we have experienced dramatic increases in performance with no unexplained slow periods. Also, before we added WITH RECOMPILE, the processing would occasionally be unevenly distributed between the eight CPUs on the SQL Server. One of the eight would get pegged while the otheres were not. After making the change we have seen an even distribution among all CPUs. Now it's taking the same amount of time to retrieve the data from the report - around 13 seconds.
|||Thanks for getting back with the solution buckenn. This was really helpful.|||This is FANTASTIC!!
I was experiencing the same problem - the stored proc would run quickly, however, the report would take up to 15 minutes - and there wasn't much data being returned - fewer than 30 rows and only 15 columns. After adding WITH RECOMPILE to the proc, now the report generates in 15 seconds.
I set up a trace, launced the report and ran the SP from with SQL MS, then stopped the trace when the report came back. Instead of requesting a whole year's worth of data (which can take over 10 minute), I used a month. It takes about 15 times longer to run the report via RS than it does from within SQL MS.
When ran via RS, there are over 50x the number of reads.
From trace log:
This is the query ran from within MS:
exec SalesByDivision
@.beginDate='2006-12-31 00:00:00:000',
@.endDate='2007-02-03 00:00:00:000',
@.lyBeginDate='2006-01-01 00:00:00:000',
@.lyEndDate='2006-01-28 00:00:00:000',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 10975 reads, 0 writes, and it took 1001 milliseconds
This is the query ran by the report server:
exec SalesByDivision
@.beginDate=''2006-12-31 00:00:00:000'',
@.endDate=''2007-02-03 00:00:00:000'',
@.lyBeginDate=''2006-01-01 00:00:00:000'',
@.lyEndDate=''2006-01-28 00:00:00:000'',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 601409 reads, 0 writes, and it took 15326 milliseconds
Did you try to just copy the exact text from the profiler? (to ensure that typo's and oversights aren't an issue)
Also, is the amount of system resources available possibly an issue?
Meaning, when running with just SQL MS, you are just using sql, but when you introduce RS, that's another program eating resources as well.
Thanks for the questions.
Yes, I copied the SQL from profiler then ran that in SQL MS.
These results are from a new production box. The system is consistently under load. Most reports that users are running are for very small date ranges and return within an acceptable time. However, reports with longer date ranges (the ones that managers and execs often run) are noticeby slower. Whether I run the SP from SQL MS or if I run a report, I assume the same resources are being used by RS.
|||There is only one version of the stored proc. For kicks I tried updating a report to include dbo. in front of the SP name. There was no noticable difference in load time.|||but add in t-sql commands to your trace. Do you know how to do this?|||WITH RECOMPILE
Another one of our developers found a forum where someone had posted the same issue. Their suggestion was to add WITH RECOMPILE to our stored proc. After doing this we have experienced dramatic increases in performance with no unexplained slow periods. Also, before we added WITHRECOMPILE, the processing would occasionally be unevenly distributed between the eight CPUs on the SQL Server. One of the eight would get pegged while the otheres were not. After making the change we have seen an even distribution among all CPUs. Now it's taking the same amount of time to retrieve the data from the report - around 13 seconds.
|||Thanks for getting back with the solution buckenn. This was really helpful.|||This is FANTASTIC!!
I was experiencing the same problem - the stored proc would run quickly, however, the report would take up to 15 minutes - and there wasn't much data being returned - fewer than 30 rows and only 15 columns. After adding WITH RECOMPILE to the proc, now the report generates in 15 seconds.
I set up a trace, launced the report and ran the SP from with SQL MS, then stopped the trace when the report came back. Instead of requesting a whole year's worth of data (which can take over 10 minute), I used a month. It takes about 15 times longer to run the report via RS than it does from within SQL MS.
When ran via RS, there are over 50x the number of reads.
From trace log:
This is the query ran from within MS:
exec SalesByDivision
@.beginDate='2006-12-31 00:00:00:000',
@.endDate='2007-02-03 00:00:00:000',
@.lyBeginDate='2006-01-01 00:00:00:000',
@.lyEndDate='2006-01-28 00:00:00:000',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 10975 reads, 0 writes, and it took 1001 milliseconds
This is the query ran by the report server:
exec SalesByDivision
@.beginDate=''2006-12-31 00:00:00:000'',
@.endDate=''2007-02-03 00:00:00:000'',
@.lyBeginDate=''2006-01-01 00:00:00:000'',
@.lyEndDate=''2006-01-28 00:00:00:000'',
@.storesListName=N'Comp stores',
@.departmentsListName=N'Owned merchandise',
@.storesCustomList=N'',
@.departmentsCustomList=N'',
@.delimiter=N',',
@.doNotRestate53WeekYear=N'0'
There were 601409 reads, 0 writes, and it took 15326 milliseconds
Did you try to just copy the exact text from the profiler? (to ensure that typo's and oversights aren't an issue)
Also, is the amount of system resources available possibly an issue?
Meaning, when running with just SQL MS, you are just using sql, but when you introduce RS, that's another program eating resources as well.
Thanks for the questions.
Yes, I copied the SQL from profiler then ran that in SQL MS.
These results are from a new production box. The system is consistently under load. Most reports that users are running are for very small date ranges and return within an acceptable time. However, reports with longer date ranges (the ones that managers and execs often run) are noticeby slower. Whether I run the SP from SQL MS or if I run a report, I assume the same resources are being used by RS.
|||There is only one version of the stored proc. For kicks I tried updating a report to include dbo. in front of the SP name. There was no noticable difference in load time.|||but add in t-sql commands to your trace. Do you know how to do this?|||WITH RECOMPILE
Another one of our developers found a forum where someone had posted the same issue. Their suggestion was to add WITH RECOMPILE to our stored proc. After doing this we have experienced dramatic increases in performance with no unexplained slow periods. Also, before we added WITH RECOMPILE, the processing would occasionally be unevenly distributed between the eight CPUs on the SQL Server. One of the eight would get pegged while the otheres were not. After making the change we have seen an even distribution among all CPUs. Now it's taking the same amount of time to retrieve the data from the report - around 13 seconds.
|||Thanks for getting back with the solution buckenn. This was really helpful.|||This is FANTASTIC!!
I was experiencing the same problem - the stored proc would run quickly, however, the report would take up to 15 minutes - and there wasn't much data being returned - fewer than 30 rows and only 15 columns. After adding WITH RECOMPILE to the proc, now the report generates in 15 seconds.
I published my website directly to its production folder then opened Sql Express Management Studio and attached the ASPNETDB file to it in that folder. However, the location information displayed in the Databases window shows the file is actually mapped back to my development folder. ?
Has anyone else encountered this problem?
Yes, I did verify that I selected the correct location.
Okay, my mistake, it isn't actually mapped back to that file, it just names the database with the original path. Gotta wonder what genius thought that one up.