Describes the different kinds of joins used in SQL Server.
Archive of entries posted on June 2009
Rebuild and Reorganize Fragmented Indexes
This script will automatically determine whether a rebuild or a reorganize should be used according to the fragmentation of the index. It will then execute the appropriate command. Note that performing index rebuilds online during production hours will cause contention. SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(258); [...]
How to Update Statistics
There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table. EXEC sp_updatestats The other way, is to use the UPDATE STATISTICS command. This command gives much [...]
Troubleshooting SQL Server Slowness
The first step in diagnosing SQL Server Slowness is to determine the physical bottleneck with the most contention. Contention in one of the following areas does not always mean that subsystem is performing poorly. It could just as well be improper utilization due to poor tuning. Nevertheless, identifying the bottleneck is always the first place [...]
Query Which Tables are Partitioned
List out which tables are partitioned, and what partition scheme and partition function they use: select t.name as TableName, ps.name as PartitionScheme, ps.data_space_id, pf.name as PartitionFunction, pf.function_id from sys.tables t join sys.indexes i on t.object_id = i.object_id join sys.partition_schemes ps on i.data_space_id = ps.data_space_id join sys.partition_functions pf on ps.function_id = pf.function_id where i.index_id < 2 [...]
Turn On Snapshot Isolation to Use Version Store
The following statement turns snapshot isolation on: ALTER DATABASE sqlserverplanet SET ALLOW_SNAPSHOT_ISOLATION ON GO ALTER DATABASE sqlserverplanet SET READ_COMMITTED_SNAPSHOT ON Once snapshot isolation is on, rows that have been modified will fill the version store. To find the amount of space the version store is using, execute: SELECT version_store_in_kb = version_store_reserved_page_count*8192/1024 FROM sys.dm_db_file_space_usage Popular search [...]
Find Number of Pages Each Database has in BufferPool
This query shows how many pages each database has in the buffer Pool. This will show you the breakdown of memory allocation for each database. SELECT DB_NAME(database_id), COUNT(page_id)as number_pages FROM sys.dm_os_buffer_descriptors WHERE database_id !=32767 GROUP BY database_id ORDER BY database_id Popular search terms:sql get number of pageshow to find how many pages in databases sql [...]
GROUP BY with LEFT JOIN
Simple GROUP BY with LEFT JOIN example: SELECT el.LogPortalName ,con.LogTypeFriendlyName ,COUNT(1) FROM dbo.EventLog el LEFT JOIN dbo.EventLogTypes con ON con.LogTypeKey = el.LogTypeKey GROUP BY el.LogPortalName, con.LogTypeFriendlyName Popular search terms:left join group bygroup by left joinleft join groupleft join with group byleft join and group by
Find Queries with Highest Reads (Highest IO)
SELECT TOP 10 ‘Procedure’ = qt.text ,DiskReads = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads ,Executions = qs.execution_count ,CPUTime = qs.total_worker_time ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY [...]
A Better sp_who2 using DMVs (sp_who3)
The following code generates the same information found in sp_who2, along with some additional troubleshooting information. It also contains the SQL Statement being run, so instead of having to execute a separate DBCC INPUTBUFFER, the statement being executed is shown in the results. Unlike sp_who2, sp_who3 only shows sessions that have a current executing request. [...]
