This tag is specifically for the R2 version of Microsoft's SQL Server 2008.
29
votes
4answers
2k views
Why does ALTER COLUMN to NOT NULL cause massive log file growth?
I have a table with 64m rows taking 4.3 GB on disk for its data.
Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text.
I added a a NULLABLE column with ...
26
votes
4answers
8k views
What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?
My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other.
Our ...
23
votes
3answers
2k views
Unexpected scans during delete operation using WHERE IN
I've got a query like the following:
DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)
...
21
votes
4answers
1k views
Still wrong to start the name of a user stored procedure with sp_?
One of my co-workers named a stored procedure in our SQL Server 2008 R2 database 'sp_something'. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
18
votes
5answers
3k views
Why is there still a varchar data type?
Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem)
After working with ...
16
votes
2answers
4k views
I/O requests taking longer than 15 seconds
Usually our weekly full backups finish in about 35 minutes, with daily diff backups finishing in ~5 minutes. Since tuesday the dailies have taken almost 4 hours to complete, way more than should be ...
15
votes
3answers
2k views
Adding columns to production tables
What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online:
The changes specified in ALTER TABLE are implemented immediately. If the ...
14
votes
3answers
984 views
Painless way to create a clustered index on a huge table?
So we have a customer site that is complaining about some seriously slow performance. I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some ...
14
votes
3answers
7k views
Execution Plan Basics — Hash Match Confusion
I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join:
select Posts.Title, Users.DisplayName
From Posts JOIN Users on
...
14
votes
1answer
3k views
SLEEP_TASK Wait Type in SQL Server - What does it indicate?
I haven't seen the SLEEP_TASK wait type before, and today I seem to be getting a ton of them.
I'm not the official DBA, just a SQL Server developer who knows some DBA stuff. We upgraded our servers ...
14
votes
1answer
731 views
Recalculate Time Dimension in BIDS
I'm using BIDS to create a cube in SSAS 2008 r2.
I used the wizard to create a time dimension. Part of that configuration is selecting a date range.
After creation, I realized that I needed a wider ...
13
votes
2answers
327 views
Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?
I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code ...
12
votes
3answers
1k views
Can you explain this execution plan?
I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects ...
12
votes
3answers
894 views
Data obfuscation in SQL Server
What is the best practice for Data Obfuscation in SQL Server?
We'd like to use masked Production data in our UAT system.
If we want to do it quickly, and with a higher level of obsfucation, what ...
12
votes
3answers
813 views
Slow deletion of records when a trigger is enabled
Thought this was solved with the link below - the work around works - but the patch doesn't. Working with Microsoft support to resolve.
http://support.microsoft.com/kb/2606883
Ok so I have an ...
12
votes
2answers
768 views
Performance Tuning for Huge Table (SQL Server 2008 R2)
Background:
I have a fact table in UAT Phase. Objective to load 5 yrs of data in Prod (expected size 400 Mn records). Currently it has only 2 years of data in Test.
Table Features:
No of ...
11
votes
3answers
456 views
Improve performance of query using IN()
I have the following SQL query:
SELECT
Event.ID,
Event.IATA,
Device.Name,
EventType.Description,
Event.Data1,
Event.Data2
Event.PLCTimeStamp,
Event.EventTypeID
FROM
Event
INNER JOIN ...
11
votes
3answers
806 views
How often to backup the master database?
The suggestion in BOL is fairly vague:
Back up master as often as necessary to protect the data sufficiently
for your business needs. We recommend a regular backup schedule, which
you can ...
11
votes
2answers
321 views
To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS
I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run ...
11
votes
3answers
640 views
Is there any benefit to defragmenting SQL indexes in a SAN environment?
Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records.
We have been running Ola Hallengren's index maintenance scripts weekly, and ...
11
votes
3answers
798 views
Does the partition key also have to be part of the primary key?
I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, ...
10
votes
2answers
203 views
What is shared between two instances on the same server?
We want to install multiple instances of SQL Server on a single Windows server. We cannot use VMs because the server is bare metal Windows 2008 R2.
For security purposes we need to ensure that the ...
10
votes
2answers
1k views
Good way to call multiple SQL Server Agent jobs sequentially from one main job?
I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to EXEC ...
10
votes
1answer
442 views
SQL Server - Anyone use SUMA, trace flag 8048, or trace flag 8015?
Recently included SQL Server startup Trace Flag 8048 to resolve a serious spinlock contention issue in a SQL Server 2008 R2 system.
Interested to hear from others who have found usage cases where ...
10
votes
5answers
2k views
SQL Server 2008 Full Text Index Never Seems to Complete
Our website has a SQL Server 2008 R2 Express Edition database with full-text indexing for our website search. Each time a new record is added or updated in one of the indexed tables, the indexing ...
9
votes
5answers
290 views
Force sql server to run query conditions as written?
Im using Sql server 2008 R2 And I have this pseudo query (SP) :
select ...
from ...
WHERE @LinkMode IS NULL
AND (myColumn IN (...very long time exeted query...))
...
...
The ...
9
votes
7answers
634 views
Version control for database objects
The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be ...
9
votes
3answers
795 views
SQL Server 2008/R2 recovery model
Pretty much all of our databases on certain servers do not require the Full Recovery model (we don't do transaction log backups) and the default should always be to create databases and specify the ...
9
votes
2answers
1k views
Restore bak file to smaller mdf and ldf database files
I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have:
MyDatabase.mdf: 24.8GB
MyDatabase.ldf: 114.6GB
This ...
9
votes
2answers
179 views
Is there a set based way to load/read a tree branch with HierarchyId
I am playing around with HierarchyId, and I have not figured out a set based way to do the following:
insert all subtree at once
retrieve all subtree at once
This question is related to my ...
9
votes
1answer
420 views
SQL Server 2008 R2 Dirty reads - how non-atomic?
I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but:
Can a row appear as ...
9
votes
1answer
269 views
Why don't SQL Server queries use more than 7MB/sec of disk I/O
I have an SSD that, using IOmeter test, shows performance over 200MB/s. However when I run any SQL query from local machine the windows resource monitor never shows disk IO above 7MB/sec. This holds ...
9
votes
1answer
376 views
query optimization: time intervals
In the main, I've got two kinds of time intervals:
presence time and absence time
absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap ...
9
votes
1answer
2k views
Stored Procedure to return dynamically created table data
Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
8
votes
2answers
1k views
Should I stop transaction log backups during a full backup?
I have two SQL Agent jobs which are scheduled to run at different intervals. The first job runs a full backup once a day. The second job runs transaction log backups every fifteen minutes.
As the ...
8
votes
4answers
622 views
Rebuilding Indexes, DB now 10x the size
I have a SQL Server database (2008 R2 SP1) that was about 15 gigs. It turns out maintenance hadn't been running in a while, so I created a maintenance plan to rebuild all the indexes, they were very ...
8
votes
2answers
493 views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should ...
8
votes
1answer
118 views
How can I prevent SSMS from re-writing my code when saving a view?
I'm creating a view that uses a statement with a WHERE clause similar to this:
WHERE
(
col1 IS NOT NULL
OR
col2 IS NOT NULL
)
AND
NOT EXISTS (SELECT ...)
...
8
votes
1answer
2k views
SQL Server 2008 Change length of varchar on live prod table
I have a MS SQL Server 2008 R2 DB server currently in use with a production app.
A new enhancement to the app now requires a varchar(100) column in a table to be increased in length.
Can the ...
8
votes
1answer
156 views
Should I be alarmed by this NO JOIN PREDICATE warning?
I'm troubleshooting the bits and pieces of a poorly-performing stored procedure. This section of the procedure is throwing a NO JOIN PREDICATE warning
select
method =
case ...
8
votes
1answer
294 views
Behavior of data in indexes based on fill factor
Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%?
From my understanding, when the data is inserted there ...
8
votes
1answer
251 views
Are SQL Server Database writes slower with Snapshot Isolation?
I have a lot of deadlocks going on in my system.
I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it.
One of his concerns is that Snapshot Isolation slows down ...
8
votes
3answers
256 views
Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format?
I hope this is a question with a shorter answer than "Read a 1000 page book", but then, if that's the real situation, then hit me with it.
I am not a real DBA, I'm a software developer who is ...
8
votes
2answers
1k views
Restore database excluding FILESTREAM data
Context
We are developing a system with a large-ish database in the bottom. It is an MS SQL database running on SQL Server 2008 R2. The total size of the database is about 12 GB.
Out of these, ...
8
votes
3answers
2k views
How to boost performance of virgin queries in MS SQL Server?
I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to ...
8
votes
1answer
197 views
What is the actual lowest possible positive REAL number
MSDN says that the range of REAL numbers is - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. Apparently the true lower limit is much lower. The following script populates a REAL column ...
8
votes
1answer
857 views
How can I add a rowversion column to a large table with minimal downtime
Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply
ALTER TABLE [Tablename]
ADD Rowversion [Rowversion] NOT NULL
Then the table is unavailable ...
8
votes
1answer
372 views
Why are there Victimless Entries on the Deadlock Graph?
I am trying to learn how to analyze SQL Server 2008's deadlock graph, and I'm finding alot of entries with an empty <victim-list> node. I don't understand what these entries represent: if there ...
8
votes
1answer
1k views
SQL Server Frozen Ghost Cleanup workaround needed
I have several tables with amount of rows between 5M and 1.5G
Each table has its BLOB field, which size varies from 100 bytes to 30 MBytes and which is stored as 'large value types out of row' = ON
...
7
votes
2answers
2k views
SQL Server 2008 - Partitioning and Clustered Indexes
So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.
Comments about how we ...
