Below query can give us execution plan of a running SPID. Clicking on XML will display the graphical plan.
select plan_handle,query_plan,TEXT from sys.dm_exec_requests
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
Below query will display the amount of free space available in every data file of a database.
SELECT name AS ‘File Name’, physical_name AS ‘Physical Name’, size/128 AS ‘Total Size in MB’,
(size/128.0)-(CAST(FILEPROPERTY(name,‘SpaceUsed’) AS int)/128.0) as ‘free space’ FROM sys.database_files
select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,’SpaceUsed’)/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) , a.NAME, a.FILENAME from dbo.sysfiles a
Below query will display the percentage of I/O for each database on a running SQL 2005/2008 system.
DB_NAME(database_id) AS database_name,
CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576.
AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS DM_IO_Stats
GROUP BY database_id
ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS row_num,
CAST(io_in_mb / SUM(io_in_mb) OVER() * 100
AS DECIMAL(5, 2)) AS pct
ORDER BY row_num;
Database mirroring can be used as a load balancing mechanism to divert all reader applications to a different instance provided that applications are modified to take advantage of the feature.
Database should be in FULL recovery model for mirroring to be configured. Once mirror is configured, transactions happening at the source will be transmitted to destination using synchronous/asynchronus mode.
Snapshots are read-only copies of a database and can be created on a schedule (once every 15 minutes/30 minutes etc). Snapshots need to be created at the mirror destination SQL instance.
Once snapshots are created, they can be used like any other database using snapshot name but applications will not be able to automatically benefit from this because original database name will be different from snapshot name.
We can create a database (Ex: master_readonly) for exclusively holding synonyms for various objects of all the databases pointing to the respective database snapshots on mirror destination SQL instance. These synonyms can be recreated as and when new snapshot is generated. This will not affect the reader queries running on mirror destination SQL instance.
A new database schema needs to be created under master_readonly database for each mirrored database and synonyms for various database objects could be created inside that database schema. Ex: If we have 10 databases that are being mirrored, 10 database schemas with various database names should be created under master_readonly database at destination SQL instance.
Summing up together, a SQL agent job could be scheduled on mirror destination SQL instance to periodically create new database snapshots, recreate synonyms to point to latest snapshots and cleanup older snapshots.
However, applications and underlying SQL code should be modified to take advantage of this setup. Application specific database views, stored procedures and user defined functions should be modified as well to follow the new pattern of master_readonly.database.object name. Following this model will seggregate reader applications from writer applications and provides for load-balancing, reduced contention, scalability.
Below things should be considered while creating clustered index on a table.
- Unique: Primary key columns that can be used to uniquely identify each row are ideal for clustered index key
- Identity columns of normalized tables based on business needs (Ex: empid for employee table, orderid for orders table) can be used as keys of clustered index
- Narrow: Clustered index key should be narrow as much as possible because it is included in all nonclustered indexes and is used for looking up data rows. A wide clustered index will increase the size of nonclustered indexes as well
- Static: Frequently updated columns are not suitable as clustered index key because whenever data changes, table has to reorganized to follow the order, nonclustered indexes also should be updated.
- Clustered index on date, identity columns will improve the performance of range queries.