The news is out. The consensus on using hints is “Do not use them”. They will ruin you. But much like the 1950’s people keep their dirty laundry to themselves and outwardly show their best “non hint” selves, this applies as well. With that said, I will go ahead and use the disclaimer that you SHOULD NOT use hints. But, if you ARE like me, and touch a lot of SQL code where there is a huge difference in site performance between a 1 second execution and 20ms execution, then sometimes you don’t have much of an option. With that said, I will talk about the optimizer. It’s getting smarter and smarter at dealing with the majority of queries and keeping them within an even keel baseline. But that comes at a cost. It can’t take as many chances so it plays it safe. And this is where you come in. But before you go around adding hints everywhere in your system (which is a horrible idea). You need to know how to write code so that it will not NEED hints. To do this is very simple. The answer is KEEP IT SIMPLE. In other words, keep the queries simple. Do not create huge SQL Statements. It’s much better to break the queries up so that you do not give the optimizer many different routes to take. Breaking them up typically entails materializing the data into temp tables (write as little data as possible) so you can break up […]
Continue reading ...
We’ve all had it happen, alerts start going off that the CPU on the SQL Server is now pegged. It’s an all hands on deck situation that you need to figure out quick. It helps to know the history of your SQL Server performance, but if you don’t there’s still hope using some troubleshooting techniques. First Make sure it’s SQL Server Sometimes there can be other processes (even backups) that are causing the CPU to push over that final edge. If you don’t have access to log into the box, the quickest way to determine how much CPU SQL Server is using is to run the following DMV: DECLARE @ts_now BIGINT SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info; SELECT record_id, dateadd(ms, -1 * (@ts_now - [TIMESTAMP]), GetDate()) AS EventTime, SQLProcessUtilization, SystemIdle, 100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization FROM ( SELECT record.VALUE('(.
)', 'int') AS record_id, record.VALUE('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)', 'int') AS SystemIdle, record.VALUE('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)', 'int') AS SQLProcessUtilization, TIMESTAMP FROM ( SELECT TIMESTAMP, CONVERT(xml, record) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%<SystemHealth>%') AS x ) AS y ORDER BY record_id DESC; If the OtherProcessUtilization column has a high value then you know you need to log into the box, open task manager and figure out what is hosing your system. It […]
There are two different functions that can be used when converting an integer to a string. One is CAST and the other is CONVERT. Either of these functions can be used with the exact same result when converting to a string. The only difference with the CONVERT function is that it takes an extra optional parameter for style which can affect the way certain data types are displayed after they are converted (an example is with date/time formats). The common need to convert an INT to a string is to then concatenate it with either another int or an existing string. Here is a simple example: SELECT CAST(12345 AS VARCHAR(11)) And the output: Here we are casting the int to a varchar(11). This is a safe value for us to convert to because the maximum integer value is -2147483648. We see if we try to convert an integer to a smaller string, it returns back an asterisks (*) meaning an error has occurred. SELECT CAST(-2147483648 AS VARCHAR(10)) So it is important to choose a data type that is large enough. With that said, I have seen the following work as well, however I personally would not run this in production in case it is deprecated in a future version of SQL. SELECT CAST(-2147483648 AS VARCHAR)
Continue reading ...
A very common requirement with SQL Server is to export a table to a csv file. It’s actually an easy task when using SQL Server Management Studio (SSMS). The export method actually behind the scenes uses SSIS wizard which also gives you an option to save the package that performs the export, however saving the package is often not necessary. The permissions you need to perform this, are merely read permissions on the table. Here are the steps broken out in detail. Right-click on the database that contains the table and choose tasks -> Export Data… Click next past the intro screen and on the “Choose a Data Source Screen” make sure the database that contains your table is listed in the drop down. Then Select Next. On the “Choose a Destination” Window, select “Flat File Destination” in the top drop down box labeled “Destination”. On the same “Choose a Destination” Window, click the “Browse” button and the Open file dialog box appears prompting you to find a location for your file. Find the location, then type in the file name. THEN to the right of the file name text box, change the drop down from “Text Files (*.txt)” to “CSV Files (*.csv)” As shown below. While still on the same “Choose a Destination” window, select whether you want the column names to be included in the CSV by selecting the “Column names in first data row” check box. Then Select Next. On the “Specify Table Copy or Query” window […]
This has to be one of the most common repetitive functions needed for development. Updating a certain column in a specific table and want to find all the procedures that are affected by that? You’ll need to use an object search. There are a few different methods for doing this. The ANSI standard method using the INFORMATION_SCHEMA.ROUTINES system views have not in the past contained all of the stored procedure text. Only the first 8000 characters or so. (If I’m wrong on that, don’t challenge me because I don’t care). Simple snippet SELECT sm.*, so.* FROM sys.sql_modules sm WITH (NOLOCK) INNER JOIN sys.objects so WITH (NOLOCK) ON sm.[object_id] = so.[object_id] WHERE sm.definition LIKE '%table_name%' However if you’re going to be using this search a lot, and I can’t imagine you’re not, then why not encapsulate it in a stored procedure so you can include multiple terms, exclude terms, and sort. You will have to place this procedure in each database you want to search however. Stored Procedure: sp_search USE master GO CREATE PROCEDURE spsearch @include VARCHAR(MAX) = NULL, @sort VARCHAR(5) = 1, -- 1 ='name_asc' 2 = 'name_desc' 3 = 'date_desc', 4 = 'date_asc' @exclude VARCHAR(MAX) = NULL AS -- EXEC sp_search 'UPDATE,products,status', 3 BEGIN SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; DECLARE @Delimiter VARCHAR(MAX) = ',' IF OBJECT_ID(N'tempdb..#split') IS NOT NULL DROP TABLE #split ;WITH split(stpos,endpos) AS( SELECT […]
Continue reading ...