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 readingBy SQLRx Admin | ColumnStore , SQL Administration , SQL Server , Tip of the Month
Find out if any of your tables contain special columnstore or spatial indexes. Columnstore indexes organizes the data in columns instead of rows like traditional indexes and can increase performance on large data sets as found in data warehouses. Spatial indexes are a special type of index on a spatial column such as geometry or […]
Continue readingBy SQLRx Admin | Database Mail , SQL Administration , SQL Server , Tip of the Month
If you are planning on installing SQL Server 2016, take note that you need SP1 or .NET 3.5 for Database Mail. Database Mail is a very important and widely used part of SQL that allows us to send e-mail. I found this when mail could not be sent and the Database Mail log was empty […]
Continue readingBy 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 readingBy 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 readingBy 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 readingBy SQLRx Admin | Advice , Beginner , Miscellaneous , SQL Administration , SQL Server , Tip of the Month
If you have Full Text catalogs set up in your databases you may notice that inside your log folder there will be lots of files named SQLFTxxxxxxxxxxxx.LOG. These are known as crawl logs which are designed to log information on the full text indexing. These logs can really pile up and there is no automated […]
Continue readingBy SQLRx Admin | Intermediate , Performance Tuning , SQL Server , Tip of the Month
SQL Server Performance Tuning: New function in SQL 2016: sys.dm_exec_function_stats Returns performance statistics for cached functions. Returns information about scalar functions, including in-memory functions and CLR scalar functions but not about table valued functions. The view returns one row for each cached function plan, and the lifetime of the row is as long as the […]
Continue readingBy SQLRx Admin | Intermediate , SQL Server , Tip of the Month
January 2016 Tip of the Month: Using sys.dm_server_memory_dumps to find SQL memory dumps. When SQL experiences a serious issue it will often dump the contents of its memory to disk. This is known as a memory dump and it does generate files that are usually located in the Logs folder. I often find that many […]
Continue reading