June 10, 2011 at 12:57 pm
I'm running the following query to get the largest tables in a database:
--Returns largest tables and indexes (by page count) in descending order
--Run in desired database
SELECT
object_name(p.object_id) as table_name
,i.[name] AS index_name
,f.name as fileGroupName
,p.used_page_count
,p.row_count
,p.used_page_count * 8192 / (1024 * 1024) AS Size_MB
FROM
sys.dm_db_partition_stats p
INNER JOIN
sys.sysindexes as i
ON
i.id = p.object_id
and i.indid = p.index_id
INNER JOIN
sys.filegroups f
ON
i.groupid = f.data_space_id
ORDER BY
p.used_page_count DESC;
Based on the results of this query, I have an index with size 25 GB in the IDX filegroup.
However, when I run the following query to get the size of the IDX file, it is only 13 GB!
--http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!7053.entry
-- File Names and Paths for TempDB and all user databases in instance
SELECT
DB_NAME([database_id])AS [Database Name],
[file_id],
name,
physical_name,
type_desc,
state_desc,
size * 8 as
FROM
sys.master_files
WHERE
DB_NAME([database_id]) = 'myDB'
Also, from visual inspection of the Windows file, the size is indeed only 13 GB.
So something is wrong with my 1st query.
Any ideas anyone?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 10, 2011 at 2:16 pm
Was this database upgraded from SQL 2000? I would try running DBCC UPDATEUSAGE and then rerunning the first query. The first query appears correct to me.
June 10, 2011 at 2:18 pm
No, this database was never on SQL 2000. It started off as a SQL-2005 database.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 14, 2011 at 2:31 pm
Any more thoughts anyone? This is very bizarre...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 14, 2011 at 2:39 pm
Here is some more info on this, just to give everyone a better idea on what is going on.
Results from sp_helpfile (focusing on the PRIMARY and IDX filegroups of the database to make things clearer):
filegroup: PRIMARY
size: 54,518 MB
filegroup: IDX
size: 13,170 MB
Running following query on PRIMARY filegroup:
SELECT
SUM(p.used_page_count * 8192 / (1024 * 1024)) AS Total_Size_MB
FROM
sys.dm_db_partition_stats p
INNER JOIN
sys.indexes AS I
ON
I.[object_id] = p.[object_id]
and I.index_id = p.index_id
INNER JOIN
sys.filegroups F
ON
I.data_space_id = F.data_space_id
WHERE
F.[name] = 'PRIMARY' ;
I get: 10,877 MB
Running same query on IDX, I get 52,313 MB!
What am I doing wrong? :w00t:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 16, 2011 at 9:04 am
Oh well, I guess I will open a PSS case with Microsoft... 😎
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply