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.
Continue reading ...
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 […]
Continue reading ...
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 ...
SQL Server Denali (2012) now has additional features built into the ORDER BY Clause. This features makes pagination simpler and more efficient. Here is an example: [cc lang=”sql”] SELECT * FROM HumanResources.Employee E ORDER BY E.EmployeeID ASC OFFSET 55 ROWS FETCH NEXT 30 ROWS ONLY; [/cc] Offset determines the start row to begin on, while the fetch next defines how many rows should be returned. Both parameters above can be variable driven. An example of this would be the following: [cc lang=”sql”] CREATE PROCEDURE dbo.spGetEmployees ( @StartRow int = 1, @RowCount int = 10 ) AS BEGIN SELECT * FROM HumanResources.Employee E ORDER BY E.EmployeeID ASC OFFSET @StartRow ROWS FETCH NEXT @RowCount ROWS ONLY; END [/cc] As you can see, this new syntax makes pagination much simpler than having to roll your own in previous versions.
Continue reading ...
