October 19, 2011 at 10:43 am
SQLRNNR (10/19/2011)
Run the scripts for parts I and II from this article and let us know which script it correlates to in your server (of the two you mentioned)
Still fails on my CS server. Didn't I send you an updated version of that script (or real similar).
October 19, 2011 at 10:45 am
got enough space scripts yet? I have some more 😀 (one of 'em written by Jeff)
---------------------------------------------------------------------
October 19, 2011 at 10:51 am
Ninja's_RGR'us (10/19/2011)
SQLRNNR (10/19/2011)
Run the scripts for parts I and II from this article and let us know which script it correlates to in your server (of the two you mentioned)Still fails on my CS server. Didn't I send you an updated version of that script (or real similar).
Fixed to work on CS server.
I still don't like the space used %. It's based on the total data files rather than used space in the data files.
BEGIN TRAN
DECLARE @dbsize DECIMAL(19,2)
,@logsize DECIMAL(19,2)
SET NOCOUNT ON
/*
** Summary data.
*/
BEGIN
SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
, @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN SIZE ELSE 0 END)) * 8/1024
FROM sys.database_files
END
;WITH FirstPass AS (
SELECT object_id,
ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024,
PageCnt = SUM(
CONVERT(DECIMAL(19,2),CASE
WHEN (index_id < 2)
THEN (used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)) * 8/1024,
RowCnt = SUM(
CASE
WHEN (index_id < 2)
THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
Where OBJECTPROPERTY(object_id,'IsMSShipped') = 0
GROUP BY object_id
)
,InternalTables AS (
SELECT ps.object_id,
ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024
FROM sys.dm_db_partition_stats ps
INNER Join sys.internal_tables it
ON it.object_id = ps.object_id
And it.internal_type IN (202,204,211,212,213,214,215,216)
WHERE it.parent_id = ps.object_id
And OBJECTPROPERTY(ps.object_id,'IsMSShipped') = 0
GROUP BY ps.object_id
)
,Summary AS (
SELECT
ObjName = OBJECT_NAME (F.object_id),
NumRows = MAX(F.RowCnt),
ReservedPageMB = SUM(IsNull(F.ReservedPage,0) + IsNull(i.ReservedPage,0)),
DataSizeMB = SUM(F.PageCnt),
IndexSizeMB = SUM(CASE WHEN (F.UsedPage + IsNull(i.UsedPage,0)) > F.PageCnt
THEN ((F.UsedPage + IsNull(i.UsedPage,0)) - F.PageCnt) ELSE 0 END) ,-- Equivalent of max_record_size from sys.dm_db_index_physical_stats
UnusedSpace = SUM(CASE WHEN (F.ReservedPage + IsNull(i.ReservedPage,0)) > (F.UsedPage + IsNull(i.UsedPage,0))
THEN ((F.ReservedPage + IsNull(i.ReservedPage,0)) - (F.UsedPage + IsNull(i.UsedPage,0))) ELSE 0 END),
dbsizeMB = @dbsize,
LogSizeMB = @logsize
FROM FirstPass F
LEFT Outer Join InternalTables i
ON i.object_id = F.object_id
GROUP BY F.object_id
)
SELECT ObjName,NumRows, ReservedPageMB, DataSizeMB, IndexSizeMB, UnusedSpace, dbsizeMB, LogSizeMB,
PercentofDB = ((IndexSizeMB + DataSizeMB) / @dbsize) * 100
FROM Summary
ORDER BY PercentofDB DESC
ROLLBACK
October 19, 2011 at 11:20 am
Ninja's_RGR'us (10/19/2011)
Ninja's_RGR'us (10/19/2011)
SQLRNNR (10/19/2011)
Run the scripts for parts I and II from this article and let us know which script it correlates to in your server (of the two you mentioned)Still fails on my CS server. Didn't I send you an updated version of that script (or real similar).
Fixed to work on CS server.
I still don't like the space used %. It's based on the total data files rather than used space in the data files.
BEGIN TRANDECLARE @dbsize DECIMAL(19,2)
,@logsize DECIMAL(19,2)
SET NOCOUNT ON
/*
** Summary data.
*/
BEGIN
SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
, @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN SIZE ELSE 0 END)) * 8/1024
FROM sys.database_files
END
;WITH FirstPass AS (
SELECT object_id,
ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024,
PageCnt = SUM(
CONVERT(DECIMAL(19,2),CASE
WHEN (index_id < 2)
THEN (used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)) * 8/1024,
RowCnt = SUM(
CASE
WHEN (index_id < 2)
THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
Where OBJECTPROPERTY(object_id,'IsMSShipped') = 0
GROUP BY object_id
)
,InternalTables AS (
SELECT ps.object_id,
ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024
FROM sys.dm_db_partition_stats ps
INNER Join sys.internal_tables it
ON it.object_id = ps.object_id
And it.internal_type IN (202,204,211,212,213,214,215,216)
WHERE it.parent_id = ps.object_id
And OBJECTPROPERTY(ps.object_id,'IsMSShipped') = 0
GROUP BY ps.object_id
)
,Summary AS (
SELECT
ObjName = OBJECT_NAME (F.object_id),
NumRows = MAX(F.RowCnt),
ReservedPageMB = SUM(IsNull(F.ReservedPage,0) + IsNull(i.ReservedPage,0)),
DataSizeMB = SUM(F.PageCnt),
IndexSizeMB = SUM(CASE WHEN (F.UsedPage + IsNull(i.UsedPage,0)) > F.PageCnt
THEN ((F.UsedPage + IsNull(i.UsedPage,0)) - F.PageCnt) ELSE 0 END) ,-- Equivalent of max_record_size from sys.dm_db_index_physical_stats
UnusedSpace = SUM(CASE WHEN (F.ReservedPage + IsNull(i.ReservedPage,0)) > (F.UsedPage + IsNull(i.UsedPage,0))
THEN ((F.ReservedPage + IsNull(i.ReservedPage,0)) - (F.UsedPage + IsNull(i.UsedPage,0))) ELSE 0 END),
dbsizeMB = @dbsize,
LogSizeMB = @logsize
FROM FirstPass F
LEFT Outer Join InternalTables i
ON i.object_id = F.object_id
GROUP BY F.object_id
)
SELECT ObjName,NumRows, ReservedPageMB, DataSizeMB, IndexSizeMB, UnusedSpace, dbsizeMB, LogSizeMB,
PercentofDB = ((IndexSizeMB + DataSizeMB) / @dbsize) * 100
FROM Summary
ORDER BY PercentofDB DESC
ROLLBACK
haha - I think you did, but I never got around to re-publishing it - d'oh
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 19, 2011 at 11:24 am
I know I did. Just not sure if it was this exact version :-D.
October 19, 2011 at 11:47 am
Here is a script I use to identify the unused space allocated to a table. I have used this to identify HEAPS that have a lot of unused space.
Use {your db here};
Go
With partitionStats (object_id, rows, reserved, data, used)
As (
Select ps.object_id
,sum(Case When ps.index_id < 2 Then row_count Else 0 End) As [rows]
,sum(ps.reserved_page_count) As reserved
,sum(Case When ps.index_id < 2
Then ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count
Else ps.lob_used_page_count + ps.row_overflow_used_page_count
End ) As data
,sum(ps.used_page_count) As used
From sys.dm_db_partition_stats ps
Group By
ps.object_id
)
, internalTables (parent_id, reserved, used)
As (
Select it.parent_id
,sum(ps.reserved_page_count) As reserved
,sum(ps.used_page_count) As used
From sys.dm_db_partition_stats ps
Inner Join sys.internal_tables it On it.object_id = ps.object_id
Where it.internal_type In (202, 204)
Group By
it.parent_id
)
Select a3.name As [schemaname]
,a2.name As [tablename]
,a1.rows As row_count
,(a1.reserved + isnull(a4.reserved, 0)) / 128.0 As 'Reserved (MB)'
,a1.data / 128.0 As 'Data (MB)'
,Case When (a1.used + isnull(a4.used, 0)) > a1.data
Then (a1.used + isnull(a4.used, 0)) - a1.data
Else 0
End / 128.0 As 'Index (MB)'
,Case When (a1.reserved + isnull(a4.reserved, 0)) > a1.used
Then (a1.reserved + isnull(a4.reserved, 0)) - a1.used
Else 0
End / 128.0 As 'Unused (MB)'
From partitionStats a1
Left Join internalTables a4 On a4.parent_id = a1.object_id
Inner Join sys.all_objects a2 On a1.object_id = a2.object_id
Inner Join sys.schemas a3 On a2.schema_id = a3.schema_id
Where a2.type <> N'S'
And a2.type <> N'IT'
Order By
'Unused (MB)' desc;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 19, 2011 at 1:08 pm
SQLRNNR (10/19/2011)
Run the scripts for parts I and II from this article and let us know which script it correlates to in your server (of the two you mentioned)
Here's the first 20 or so:
ObjName NumRows ReservedPageMB DataSizeMB IndexSizeMB UnusedSpace DBSizeMB LogSizeMB PercentofDB
----------------------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
sysdercv 41267531 13886.4765625 13884.625000 0.000000 1.8515625 21468.06 1560.69 64.675700
sysdesend 41267531 2704.2890625 2703.976562 0.000001 0.3125000 21468.06 1560.69 12.595300
sysconvgroup 41267531 1489.7890625 1489.601562 0.000001 0.1875000 21468.06 1560.69 6.938600
tbl_order_audits 8890138 910.5468750 780.992187 129.296876 0.2578125 21468.06 1560.69 4.240200
tbl_orders 169423 193.7421875 140.570312 52.679688 0.4921875 21468.06 1560.69 0.900100
tbl_attribute_answers 454593 71.0937500 36.890625 33.851563 0.3515625 21468.06 1560.69 0.329500
tbl_alerts 261062 39.0234375 35.750000 3.125000 0.1484375 21468.06 1560.69 0.181000
tbl_financial_alert_log 168365 36.1953125 34.203125 1.804688 0.1875000 21468.06 1560.69 0.167700
tbl_alert_log 167015 36.1875000 34.195312 1.796876 0.1953125 21468.06 1560.69 0.167600
tbl_audit 404237 31.3828125 31.343750 0.000000 0.0390625 21468.06 1560.69 0.146000
tbl_customers 153521 28.9453125 28.945312 0.000001 0.0000000 21468.06 1560.69 0.134800
tbl_memory_widget_usage 103290 27.2890625 27.281250 0.000000 0.0078125 21468.06 1560.69 0.127000
tbl_Partner_Invt_xref 124025 23.1406250 6.148437 16.781251 0.2109375 21468.06 1560.69 0.106800
tbl_pricing_history 273689 21.5156250 17.296875 4.140625 0.0781250 21468.06 1560.69 0.099800
trace 37242 20.3906250 20.351562 0.000001 0.0390625 21468.06 1560.69 0.094700
tbl_order_details 188607 23.0781250 10.210937 9.382813 3.4843750 21468.06 1560.69 0.091200
tbl_shopping_cart_backup 211462 19.3906250 19.390625 0.000000 0.0000000 21468.06 1560.69 0.090300
tbl_values 138697 18.5000000 9.617187 8.734376 0.1484375 21468.06 1560.69 0.085400
tbl_attribute_answers_alt 285347 16.2656250 9.515625 6.476563 0.2734375 21468.06 1560.69 0.074400
tbl_products 21215 12.6953125 11.304687 1.226563 0.1640625 21468.06 1560.69 0.058300
tbl_products_backup 20212 9.7656250 9.718750 0.000000 0.0468750 21468.06 1560.69 0.045200
tbl_product_category_xref 100516 9.5234375 3.710937 5.546876 0.2656250 21468.06 1560.69 0.043100
sysobjvalues 1660 9.4765625 7.421875 0.000000 2.0546875 21468.06 1560.69 0.034500
tbl_log 125551 6.2578125 6.257812 0.000001 0.0000000 21468.06 1560.69 0.029100
tbl_categories 24079 6.5390625 3.070312 3.015626 0.4531250 21468.06 1560.69 0.028300
October 19, 2011 at 1:12 pm
Jeffrey Williams 3188 (10/19/2011)
Here is a script I use to identify the unused space allocated to a table. I have used this to identify HEAPS that have a lot of unused space.
Use {your db here};
Go
With partitionStats (object_id, rows, reserved, data, used)
As (
Select ps.object_id
,sum(Case When ps.index_id < 2 Then row_count Else 0 End) As [rows]
,sum(ps.reserved_page_count) As reserved
,sum(Case When ps.index_id < 2
Then ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count
Else ps.lob_used_page_count + ps.row_overflow_used_page_count
End ) As data
,sum(ps.used_page_count) As used
From sys.dm_db_partition_stats ps
Group By
ps.object_id
)
, internalTables (parent_id, reserved, used)
As (
Select it.parent_id
,sum(ps.reserved_page_count) As reserved
,sum(ps.used_page_count) As used
From sys.dm_db_partition_stats ps
Inner Join sys.internal_tables it On it.object_id = ps.object_id
Where it.internal_type In (202, 204)
Group By
it.parent_id
)
Select a3.name As [schemaname]
,a2.name As [tablename]
,a1.rows As row_count
,(a1.reserved + isnull(a4.reserved, 0)) / 128.0 As 'Reserved (MB)'
,a1.data / 128.0 As 'Data (MB)'
,Case When (a1.used + isnull(a4.used, 0)) > a1.data
Then (a1.used + isnull(a4.used, 0)) - a1.data
Else 0
End / 128.0 As 'Index (MB)'
,Case When (a1.reserved + isnull(a4.reserved, 0)) > a1.used
Then (a1.reserved + isnull(a4.reserved, 0)) - a1.used
Else 0
End / 128.0 As 'Unused (MB)'
From partitionStats a1
Left Join internalTables a4 On a4.parent_id = a1.object_id
Inner Join sys.all_objects a2 On a1.object_id = a2.object_id
Inner Join sys.schemas a3 On a2.schema_id = a3.schema_id
Where a2.type <> N'S'
And a2.type <> N'IT'
Order By
'Unused (MB)' desc;
This returns roughly the same results as the others, we have one table with 8.8 million rows and everything else is less than 250k
October 19, 2011 at 1:16 pm
Ninja's_RGR'us (10/19/2011)
Run the code found here and post the results => http://sqlskills.com/BLOGS/PAUL/post/Survey-nonclustered-index-counts-(code-to-run).aspxWho was working during the weekend and what where they doing?
17 GB just doesn't happen overnight.
You're kidding right? I'm an ecomm developer I don't work weekends 😎
Here's the results for this code:
BaseType NCIndexes TableCount
---------- --------- -----------
Clustered 0 396
Clustered 1 82
Clustered 2 25
Clustered 3 12
Clustered 4 13
Clustered 5 2
Clustered 6 5
Heap 0 155
Heap 1 10
Heap 2 8
Heap 3 2
October 19, 2011 at 1:19 pm
I think this may have helped:
sysdercv 41267531 13886.4765625
A quick search for "sysdercv" tells me that table is for service broker. I'm going to check the service broker messages and see if something has them backed up like mad.
October 19, 2011 at 1:24 pm
mwagner 55444 (10/19/2011)
I think this may have helped:
sysdercv 41267531 13886.4765625
A quick search for "sysdercv" tells me that table is for service broker. I'm going to check the service broker messages and see if something has them backed up like mad.
Yeah - it is backed up, you will want to clear that out.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 19, 2011 at 1:44 pm
I just did
SELECT * FROM [Queue Name]
For every service broker queue in the database and only found 9 messages in one queue...
I even tried:
DECLARE @msg TABLE (
message_body XML
);
WAITFOR (RECEIVE message_body FROM [SendEmailQueue] INTO @msg), TIMEOUT 1000;
SELECT * FROM @msg;
For each queue and after clearing out the 9 messages in the one queue this came up empty...
So now how do I check / clear service broker messages other than the way I just did?
October 19, 2011 at 1:54 pm
It looks to be a need to End the Conversation (service broker).
Check this thread and see what they did.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 19, 2011 at 2:24 pm
SQLRNNR (10/19/2011)
It looks to be a need to End the Conversation (service broker).Check this thread and see what they did.
That is the exactly the problem, I've got something looping through now ending conversations. I found this page helpful:
http://myadventuresincoding.wordpress.com/2007/11/22/sql-server-service-broker-tips-and-tricks/
October 19, 2011 at 2:27 pm
mwagner 55444 (10/19/2011)
SQLRNNR (10/19/2011)
It looks to be a need to End the Conversation (service broker).Check this thread and see what they did.
That is the exactly the problem, I've got something looping through now ending conversations. I found this page helpful:
http://myadventuresincoding.wordpress.com/2007/11/22/sql-server-service-broker-tips-and-tricks/
Most excellent.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply