With SQL Server 2005+, it is very easy to view the specifics of connection information. This is very useful because when troubleshooting slowdowns. Luckily there are a few dynamic management views that provide insight into connection and session information. The following query groups the connections according the program that is connected to SQL Server. This information can be spoofed however using a connection string. When running this query, you will find how important it is to add the application name to the query string. The query also shows the number of connections opened by each application. [cc lang=”sql”] — By Application SELECT CPU = SUM(cpu_time) ,WaitTime = SUM(total_scheduled_time) ,ElapsedTime = SUM(total_elapsed_time) ,Reads = SUM(num_reads) ,Writes = SUM(num_writes) ,Connections = COUNT(1) ,Program = program_name FROM sys.dm_exec_connections con LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = con.session_id GROUP BY program_name ORDER BY cpu DESC [/cc] This next query groups the same information by user: [cc lang=”sql”] — Group By User SELECT CPU = SUM(cpu_time) ,WaitTime = SUM(total_scheduled_time) ,ElapsedTime = SUM(total_elapsed_time) ,Reads = SUM(num_reads) ,Writes = SUM(num_writes) ,Connections = COUNT(1) ,[login] = original_login_name from sys.dm_exec_connections con LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = con.session_id GROUP BY original_login_name [/cc] By using sp_who3, you can also find the exact statements running, along with more detailed information.
Continue reading ...
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This error message is due to the server setting of Remote Connection Query Timeout. The default is 600 seconds, or 10 minutes. [cc lang=”sql”] EXEC SP_CONFIGURE ‘remote query timeout’, 1800 reconfigure EXEC sp_configure EXEC SP_CONFIGURE ‘show advanced options’, 1 reconfigure EXEC sp_configure EXEC SP_CONFIGURE ‘remote query timeout’, 1800 reconfigure EXEC sp_configure [/cc] After making this change, make sure to close the window and create a new connection in order to inherit the new query timeout settings.
One new feature that SQL Server 2008 offers is more reliable dependency information. SQL Server 2005 offered a DMV (dynamic management view) called sys.sql_dependencies. It is now replaced by a more reliable sys.sql_expression_dependencies. The following script will show all the procedures that reference a given table name, along with the columns the procedure references also. Please note, this does not take into account any dynamic SQL. [cc lang=”sql”] USE MYDatabase GO DECLARE @TableName varchar(100) SET @TableName = ‘mytable’ SELECT SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id) ,SourceObject = OBJECT_NAME(sed.referencing_id) ,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME()) ,ReferencedSchema = ISNULL(sre.referenced_schema_name, OBJECT_SCHEMA_NAME(sed.referencing_id)) ,ReferencedObject = sre.referenced_entity_name ,ReferencedColumnID = sre.referenced_minor_id ,ReferencedColumn = sre.referenced_minor_name FROM sys.sql_expression_dependencies sed CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ‘.’ + OBJECT_NAME(sed.referencing_id), ‘OBJECT’) sre WHERE sed.referenced_entity_name = @TableName AND sre.referenced_entity_name = @TableName [/cc]
Continue reading ...
[cc lang=”sql”] DROP INDEX table_name.index_name [/cc]
One of the most common database performance issue most companies face is the improper choice of indexes or the lack of indexes. Most essential, is the clustered index. A well-chosen clustered index will save CPU, Memory, and IO. Choosing the clustered index for a table goes hand-in-hand with the writing of the queries for a table. It is best to design the queries and the index simultaneously to keep consistency. The majority of the time this is not an option. When we have not written the queries, the best way to understand the optimal clustered index is to study the queries that are run against the table. Once you understand the queries that run against a table, it is helpful to think of each of these queries broken down into three parts: The select list – Be on the lookout for the queries that return the most columns from the table. The join columns – The most common columns used to join other tables against the table. The where clause – The most common columns used to filter the result sets. Avoiding Fragmentation Before going into depth in choosing a clustered index we need to know what causes fragmentation. We ideally want to answer no to the following: Will the clustered value(s) be updated? Can new rows be inserted between already inserted clustered rows? The reason for answering no above is because we do not want fragmentation at the clustered level. The clustered index is like the foundation of a […]
Continue reading ...
