Category Archives for "Helpful Scripts"

Sep 28

Get Index Column Info with Includes for One or Many Tables

By Lori Brown | Helpful Scripts , Missing Indexes , Performance Tuning , Query Tuning , SQL Administration , SQL Development , SQL Server

I was recently working with one of my clients on some low hanging fruit type of query tuning. We had checked the cache for plans with missing index warnings in them and were trying to see if we could tweak and existing index or add a new index to speed things up. If you ever […]

Continue reading
Sep 14

Find Tables That Have Special Features Enabled

By SQLRx Admin | Helpful Scripts , Miscellaneous , SQL Server , Tip of the Month

Find out if any of tables in your database have special features enabled using the queries below.  These features need to be understood and carefully managed. — CDC Enabled Tables select distinct t.name AS CDCTables from sys.tables t where t.is_tracked_by_cdc = 1   — File Tables — SQL 2012 + select distinct t.name AS FileTables from […]

Continue reading
Jun 08

Get a List of Tables That Are Compressed

By SQLRx Admin | Beginner , Helpful Scripts , SQL Administration , SQL Server , Tip of the Month

Find out if any of your tables are compressed in your database using the query below. Compressed tables can save space and make queries run faster. — Compressed Tables select distinct t.name AS CompressedTables from sys.partitions p inner join sys.tables t on p.object_id = t.object_id where p.data_compression > 0 If you don’t have any tables […]

Continue reading
May 04

List Partitioned Tables And Other Info About Them

By Lori Brown | Beginner , Helpful Scripts , Partitions , SQL Administration , SQL Server , Tip of the Month

Here is a good way to find out if any of your tables are partitioned in your database using the query below. It is important to know so that you can find out how the tables are partitioned. — Partitioned Tables SELECT DISTINCT t.name AS ParitionedTables FROM sys.partitions p INNER JOIN sys.tables t ON p.object_id […]

Continue reading
Apr 13

List Tables That May Be Over Indexed

By Lori Brown | Beginner , Helpful Scripts , Performance Tuning , SQL Administration , SQL Server , Tip of the Month

While not having enough indexes can be bad for query performance, having too many indexes can also be just as bad. Use the query below to get a list of tables in your database that has more than 10 indexes. — Tables with large number of indexes select t.name as TablesWithLargeNumInx, count(i.name) as CountIndexes from […]

Continue reading
>