;WITH task_space_usage AS (
-- SUM alloc/delloc pages
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)
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 ...
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(
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 ...
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 ...
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 ...
There are two key differences between EXCEPT and NOT IN.
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 ...
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 ...
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 ...
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 any ...
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 ...
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.
UPDATE: Old Link broken
Here is the link to the table_constraint docs that details ...
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
If your goal is to process all deletes only if they all succeed, why not just use TRY/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/...
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'
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. ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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, ...
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 ...
In SQL Server the clustered index key column(s) are always added in to the non clustered index to act as a row locator (Ref: More About Nonclustered Index Keys).
For an NCI declared as unique they are added as an included column otherwise they are added to the end of the key.
You might want to add the columns in explicitly if the default placement is not ...
The best way to convert a non-current UTC date into local time is to use the CLR. The code itself is easy; the difficult part is usually convincing people that the CLR isn't pure evil or scary...
For one of the many examples, check out Harsh Chawla's blog post on the topic.
Unfortunately, there is nothing built-in that can handle this type of conversion, ...
While shrinking is dangerous indeed for the reasons mentioned here. There is a happy medium between Jimbo's answer and John's answer... You should always seriously consider whether or not you want to shrink your database.
In an ideal world - you'd create your DB with plenty of free space to grow into. I call this "Right Sizing" your database. You would ...
@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:
Connection management in Microsoft SQL Server 2005 and later versions is based on endpoints. An endpoint is a SQL Server object that enables ...
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
----------- --------- ------------- ----------- ---------
After doing some online search,I found what you needed,hope my answer helps you.
It Only works with transactional and transactional peer to peer
T-SQL script which you can use to monitor the status of transactional replication and performance of publications and subscriptions.
Things to be considered before executing the ...