Often times when troubleshooting, it is helpful to know how much resources a particular application is requesting from your SQL Server. The easiest way to get this information is by using the connections and sessions DMV. These DMV’s when combined are powerful. Here we will query the aggregate connection information. The values below represent an aggregate value of all connections that are currently established by that application. [cc lang=”sql”] 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] Note that the above query does not break down the values per user. In order to do that, we need to create an additional grouping condition (login_name). [cc lang=”sql”] 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 ,LoginName = ses.login_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, ses.login_name ORDER BY cpu DESC [/cc]
Continue reading ...
An important step in optimizing a system is to take a holistic approach and look at stored procedures that are called very often. These procedures can often be the backbone of a system. Sometimes optimizing these procedures can have a dramatic effect on relieving the physical bottlenecks of the system and improving end-user experience. The following DMV query shows the execution count of each stored procedure, sorted by the most executed procedures first. [cc lang=”sql”] SELECT DatabaseName = DB_NAME(st.dbid) ,SchemaName = OBJECT_SCHEMA_NAME(st.objectid,dbid) ,StoredProcedure = OBJECT_NAME(st.objectid,dbid) ,ExecutionCount = MAX(cp.usecounts) FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st WHERE DB_NAME(st.dbid) IS NOT NULL AND cp.objtype = ‘proc’ GROUP BY cp.plan_handle ,DB_NAME(st.dbid) ,OBJECT_SCHEMA_NAME(objectid,st.dbid) ,OBJECT_NAME(objectid,st.dbid) ORDER BY MAX(cp.usecounts) DESC [/cc] These execution counts are an aggregate from the last time SQL Server has been restarted.
This error usually comes up when you are trying to perform a SELECT..INTO across a linked server. The problem is that a 4 part name cannot be used as the “INTO” parameter. This will fail: [cc lang=”sql”] SELECT * INTO [remoteserver].RemoteDB.dbo.sysdtspackages FROM MSDB.dbo.sysdtspackages [/cc] The trick is to create the table on the remote server, then perform an INSERT INTO. [cc lang=”sql”] INSERT INTO [remoteserver].RemoteDB.dbo.sysdtspackages SELECT * FROM MSDB.dbo.sysdtspackages [/cc]
Continue reading ...
If you read execution plans enough, you’ve probably realized that when SQL Server joins tables together, it uses different internal algorithms. The three algorithms are: Loop Join Merge Join Hash Join These alogorithms that are used are based upon factors of the underlying data. Merge Join For the most part, this is the most efficient method of joining tables. As the name implies, both tables are essentially merged together, much like a zipper being zipped. It typically occurs when both tables that are being joined, are joined on keys that are presorted and contain all the same keys in both tables (for example joining a primary key with a foreign key). When one table contains keys that the other table does not have, the chance of the merge join being used is less likely. The physical profile of a merge join is very little CPU usage, and very little reads compared to other types of joins. Loop Join The loop join is more CPU intensive than a merge join. This join typically occurs when one worktable is quite a bit smaller than the other. As the word loop implies, the smaller table being joined is looped until it finds the matching key in the outer (larger) table. This join is most efficient when the resulting output is smaller than 5000 rows. When larger than that, the CPU and reads make the join less efficient. Hash Join A hash join is the least efficient of all joins, however that does not […]
I’m hoping this is a collaborative post, because I do not know a lot about the capabilities of Denali, and the documentation looks pretty sparse. What I do know however is that everything I have seen looks promising. The SQL Server development team is top notch in my opinion. The direction they are travelling is very promising for us as SQL Server professionals. Onto the post. So I downloaded the CTP version. Which can be downloaded here Installation One note on the install. You cannot install this on Windows XP or on Server 2003. This is a little surprising because I can only imagine how many clients will not be able to upgrade. The other surprising thing is that because many companies have not adopted Windows 7 yet on their desktops (and will likely never move to Vista), the developers will not be able to install SSMS (SQL Server Management Studio). Because of this limitation, many people may find themselves calling this version SQL Server 2013. As for the actual installation, it’s getting more cumbersome, and complicated, however the new features and added security are worth it. SQL Server Management Studio The first cool part is SSMS. SSMS now boasts a “Powered by Visual Studio” text on the splash screen. Digging into it deeper, you can see the changes. The borders of the interface are a different color and the tabs that appear when you create a new query look different. The color coding is nicer looking (table names are […]
Continue reading ...
