Browse by Tags

SQL Server 2005 TSQL to obtain buffers by object (table, index) in the buffer cache?

Published 1 February 8 6:30 AM | SSQA.net

As a programmer interacting with SQL Server's cache is not often needed, but when you do need to determine what is going on with the cache, or you simply need to flush the execution plans or data pages to tune a query, you now have the means to do...

Filed under: sql server, indexes, memory, dmv, cache, buffer, table, tsql, configuration

Internal Tables in SQL Server 2005, what are they for?

Published 2 November 7 3:17 AM | SSQA.net

By default SQL Server automatically creates the internal tables for Full-text search, XML indexes and Service broker. These are also created when a user query is written poorly that uses tempdb heavily. At they are called internal tables but do not contain...

Filed under: sql server, indexes, security, search, xml, permissions, table, full-text, internal tables

DBCC EXTENTINFO - unused pages in these extents (undocumented)

Published 4 September 7 7:45 AM | SSQA.net

When it comes to resolving table fragmentation, the basic checkout you perform is to run DBCC INDEXDEFRAG or even run DBCC DBREINDEX statements. As per the default configuraiton SQL Database Engine allocates a new extent to an allocation unit only when...

Filed under: sql server, performance, indexes, data, i/o, table, insert, allocation, fragmentation, deadlock, extent

How do I find all the statistics and statistics columns on a specified object?

Published 16 July 7 7:46 AM | SSQA.net

USE <database_name>; GO SELECT s.name AS statistics_name ,c.name AS column_name ,sc.stats_column_id FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id INNER JOIN sys.columns AS c ON...

Filed under: sql server, performance, indexes, table, tsql, statistics, columns