76

Short version: seek is much better Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows ...


76

This is a decision of the cost based optimiser. The estimated costs used in this choice are incorrect as it assumes statistical independence between values in different columns. It is similar to the issue described in Row Goals Gone Rogue where the even and odd numbers are negatively correlated. It is easy to reproduce. CREATE TABLE dbo.animal( id ...


74

From http://www.sqlservercentral.com/scripts/tempdb/72007/ ;WITH task_space_usage AS ( -- SUM alloc/delloc pages SELECT session_id, request_id, SUM(internal_objects_alloc_page_count) AS alloc_pages, SUM(internal_objects_dealloc_page_count) AS dealloc_pages FROM sys.dm_db_task_space_usage WITH (NOLOCK) WHERE ...


43

Very good question as it is such a important concept. This is a big topic though and what I am going to show you is a simplification so you can understand the base concepts. Firstly when you see clustered index think table. In SQL server if a table does not contain a clustered index it is a heap. Creating a clustered index on the table actually transforms ...


35

I know this is an old thread but I would say to a large degree snapshot isolation is a magic bullet. It will eliminate all of your blocking between readers and writers. It will however not prevent writers from blocking other writers. There is no way around that. In my experience, the additional load on the TEMPDB is negligible and the benefits of row ...


33

There is a do-not-ever-do-this-in-a-live-environment hack you can use where space is limited, by restoring the log file to a compressed folder. Attempt this by compressing an existing folder and restoring to it will result in an error, so you have to cheat with a symbolic link. Create a compressed folder D:\LogCompressed\ Create a symbolic link to the ...


33

SQL Compilations/sec is a good metric, but only when coupled with Batch Requests/sec. By itself, compilations per sec doesn't really tell you much. You are seeing 170. If batch req per sec is only 200 (a little exaggerated for effect) then yes, you need to get down to the bottom of the cause (most likely an overuse of ad hoc querying and single-use ...


30

There are three main technical reasons you get the plan you do: The optimizer's costing framework has no real support for non-inline functions. It does not make any attempt to look inside the function definition to see how expensive it might be, it just assigns a very small fixed cost, and estimates the function will produce 1 row of output each time it is ...


29

Suppose I have to export data from one server to another. Best is to use IF you want all data use Backup / Restore; BCP OUT & BCP IN or SSIS IF you want subset of data (some tables only) use SSIS or BCP OUT & BCP IN TO move data, depending on the amount/size of data and n/w bandwidth, Linked server will kill the performance. Executing in ...


29

There are two key differences between EXCEPT and NOT IN. EXCEPT EXCEPT filters the DISTINCT values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing a NOT EXISTS with a DISTINCT clause. It also expects the two tables (or subset of columns from the tables) to have the same number of columns in the ...


28

As you perform inserts updates and deletes, your indexes will become fragmented both internally and externally. Internal fragmentation is you have a high percentage of free space on your index pages, meaning that SQL Server needs to read more pages when scanning the index. External fragmentation is when the pages of the index are not in order any more, so ...


27

It looks like there are multiple ways to accomplish this, but I found the simplest way was Martin's suggestion of setting up the procedure in a SQL job, and starting it using the asynchronous sp_start_job command from my stored procedure. EXEC msdb.dbo.sp_start_job @job_name='Run2ndStoredProcedure' This only works for me because I don't need to specify ...


25

This answer may prove helpful to the original question but is primarily to address inaccurate information in other posts. It also highlights a section of nonsense in BOL. And as stated for the INSERT documentation, it will acquire an exclusive lock on the table. The only way a SELECT can be made against the table is to use NOLOCK or set the isolation ...


25

You can use nested EXEC calls. The database context changed by the USE persists to the child batch. DECLARE @DB SYSNAME SET @DB = 'tempdb' DECLARE @CreateViewStatement NVARCHAR(MAX) SET @CreateViewStatement = ' USE '+ QUOTENAME(@DB) +'; EXEC('' CREATE VIEW [dbo].[MyTable] AS SELECT 1 AS [Foo] '') ...


25

You could NEVER, EVER trust Task Manager to tell you how much memory SQL Server is using (maybe you are remembering a 32-bit system with a very small amount of memory). Stop using Task Manager for this, period. Use the performance counter - you can also query the performance counter using DMVs: SELECT object_name, cntr_value FROM sys....


25

Oh, goodness, I have some bad news here. On a 32-bit OS, SQL Server only uses the first 4GB of memory for things like query workspace. (And it's fighting the OS for that 4GB, too - any other running apps will also compete for that memory.) 4GB might sound like a lot, but it's relatively easy to write a query that needs several GB of memory in order to run. ...


24

Clearly there isn't a nested CASE expression here. Not in the query text, no. But the parser always expands CASE expressions to the nested form: SELECT CASE SUBSTRING(p.Name, 1, 1) WHEN 'a' THEN '1' WHEN 'b' THEN '2' WHEN 'c' THEN '3' WHEN 'd' THEN '4' WHEN 'e' THEN '5' WHEN 'f' THEN '6' WHEN 'g'...


24

It looks like this is a cost based decision by the optimizer but a rather bad one. If you add 50000 rows to PRODUCT the optimizer thinks the scan is too much work and gives you a plan with three seeks and one call to the UDF. The plan I get for 6655 rows in PRODUCT With 50000 rows in PRODUCT I get this plan instead. I guess the cost for calling the UDF ...


23

If your goal is to process all deletes only if they all succeed, why not just use TRY/CATCH: BEGIN TRANSACTION; BEGIN TRY DELETE #1; DELETE #2; DELETE #3; COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; END CATCH If the goal is to allow all successful deletes to succeed even if one or more will fail, then you can use individual TRY/...


23

NOT FOR REPLICATION indicates that when a record is replicated to this table, any value that is inserted into the identity column via the replication agent keeps it's original value from the source system, but any records added locally still increment the identity value. The following link explains it better than I can, it's marked as SQL 2000, but as far ...


22

I tested the performance of all 3 methods, and here's what I found: 1 record: No noticeable difference 10 records: No noticeable difference 1,000 records: No noticeable difference 10,000 records: UNION subquery was a little slower. The CASE WHEN query is a little faster than the UNPIVOT one. 100,000 records: UNION subquery is significantly slower, but ...


21

For SQL Server 2005+ you can generate those 12 records very easily with a loop ar a recursive CTE. Here is an example of a recursive CTE: DECLARE @Date DATETIME SELECT @Date = '20120308 11:00:00' ;WITH Dates AS ( SELECT DATEPART(HOUR,DATEADD(HOUR,-1,@Date)) [Hour], DATEADD(HOUR,-1,@Date) [Date], 1 Num UNION ALL SELECT DATEPART(HOUR,...


21

Isolate backup I/O from the rest of the I/O on your system. While it may take longer and be more prone to hiccups, backing up over the network instead of to a local disk may help alleviate the direct impact on the instance. Even in a virtual machine you should be able to expose other storage so that SQL Server can write to a different I/O subsystem. It will ...


21

Well, you can start by installing Management Studio. (How else do you plan to perform maintenance tasks once you have access to the instance?) I recommend the most recent version of Management Studio which is fully functional, manages 2005 instances just fine, and is completely free of any licensing requirements. You can download from here: Download SQL ...


21

While I agree with other commenters that this is a computationally expensive problem, I think that there is a lot of room for improvement by tweaking the SQL that you are using. To illustrate, I create a fake data set with 15MM names and 3K phrases, ran the old approach, and ran a new approach. Full script to generate a fake data set and try out the new ...


20

First of all, apologies for such a long answer, as I feel that still there is a lot of confusion when people talk about terms like collation, sort order, code page, etc. From BOL : Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and ...


19

Do writes to TempDB always result in an actual physical write to disk, or are TempDB writes cached by SQL Server for delayed write like in the Windows file system cache? Do they always? Most definitely not. Do they ever? Yes but not as a result of the typical mechanism. Reference here is What does checkpoint do for tempdb?. In a "well behaved" system, ...


19

@mrdenny's answer is accurate that failing over one database will not result in all the other databases failing over as well. However just to give more overview of what a database mirroring Endpoint is: From BOL, Connection management in Microsoft SQL Server 2005 and later versions is based on endpoints. An endpoint is a SQL Server object that enables ...


19

There are many reasons to avoid using the system scalar function sys.fn_varbintohexstr: It is undocumented and unsupported. You won't find it in Books Online and if you report a problem with it, customer support services are not obliged to assist you. It is a T-SQL scalar function. These can be evil. Microsoft do not assign their best and brightest ...


19

Let me summarize (and round!) the important data points in your spreadsheet: Total Use Count 1 --------------------------------------- ----------------------- Total Plans Total MBs Avg Use Count Total Plans Total MBs ----------- --------- ------------- ----------- --------- ...


Only top voted, non community-wiki answers of a minimum length are eligible