Tagged Questions
40
votes
4answers
6k views
Are SQL Server in-place upgrades as ill advised as they used to be?
I have been working with SQL server on and off since SQL Server 6.5, the old advice that still rings in my head was never to do an in-place upgrade.
I'm currently upgrading my 2008 R2 DEV and TEST ...
32
votes
4answers
12k 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 ...
31
votes
8answers
7k views
Is select * still a big no-no on SQL Server 2012?
Back in the days of yesteryear, it was considered a big no-no to do select * from table or select count(*) from table because of the performance hit.
Is this still the case in later versions of SQL ...
20
votes
2answers
1k views
Why does query error with empty result set in SQL Server 2012?
When running the following queries in MS SQL Server 2012 the second query fails but not the first. Also, when run without the where clauses both queries will fail. I am at a loss why either would fail ...
17
votes
2answers
2k views
Optimising plans with XML readers
Executing the query from here to pull the deadlock events out of the default extended events session
SELECT CAST (
REPLACE (
REPLACE (
XEventData.XEvent.value ...
16
votes
1answer
4k views
Connection pools being reset with Error: 18056, Severity: 20, State: 46. & Perfmon Counters not showing
We are using SQL authentication & .net 4.0 Connection strings to connect to an Enterprise Edition 2012 SP1 SQL Server on a windows 2008r2 Enterprise Server. We use about 50 Servers split into 8 ...
14
votes
2answers
508 views
Lock CREATE TABLE
In another application I was struck by bad design: multiple threads execute an EnsureDatabaseSchemaExists() method concurrently, which looks basically like this:
IF NOT EXISTS (SELECT * FROM ...
14
votes
2answers
1k views
Why does DELETE leave a lingering effect on performance?
At the end is a test script for comparing the performance between a @table variable and a #temp table. I think I've set it up correctly - the performance timings are taken outside of the ...
14
votes
1answer
2k views
Are there disadvantages to contained databases?
SQL Server 2012 introduced the concept of "contained" databases, where everything (well, mostly everything) the database needs is contained within the database itself. This offers big advantages when ...
12
votes
5answers
5k views
What is a good, repeatable way to calculate MAXDOP on SQL Server?
When setting up a new SQL Server 2012, I use the following code to determine a good starting point for the MAXDOP setting:
/*
This will recommend a MAXDOP setting appropriate for your machine's ...
12
votes
1answer
143 views
“Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060” while altering the table
I am trying to alter a column in a table. The existing table is like this:
CREATE TABLE [dbo].[table](
[id1] [int] NOT NULL,
[id2] [int] NOT NULL,
[id3] [int] NOT NULL,
[name] ...
12
votes
4answers
12k views
SQL Server 2012 slower than 2008
I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL ...
11
votes
3answers
571 views
Get streak count and streak type from win-loss-tie data
I made a SQL Fiddle for this question if that makes things easier for anyone.
I have a fantasy sports database of sorts and what I'm trying to figure out is how to come up with "current streak" data ...
11
votes
1answer
1k views
What's the anatomy of a columnstore index?
One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index.
I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...
11
votes
1answer
1k views
Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler?
Using SQL Server Profiler (I'm on SQL Server 2012), I'm trying to generate a useful trace that shows the parameter values, not just the SQL with variable names. The stored procedure walks through a ...
11
votes
1answer
940 views
SQL Server 2012 Standard server with 50 instances won't uninstall instances
I suspect this is an issue because we hit the 50 instance max limit of SQL Server. At the limit it won't install more instances, naturally. However, it seems it won't UNinstall them either. That's ...
10
votes
4answers
6k views
SSMS Tools Pack Alternative [closed]
Are there any tools out there comparable to SSMS Tools Pack 2012? The licensing ($30 per machine, $100 for any number of machines... for 3 months) leaves a lot to be desired and I wasn't sure of what ...
9
votes
3answers
2k views
Unique Identifier with Extra Characters Still Matching in Select
We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID.
For ...
9
votes
3answers
372 views
Where to learn SQL Server Service Broker?
Are there any good resources to learn Service Broker?
I'm looking for a newbie-friendly guide.
9
votes
3answers
2k views
SQL Server Agent Jobs and Availability Groups
I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups. Maybe I missed something, however at the current state I feel that SQL Server ...
9
votes
3answers
311 views
Optimizing a CTE hierarchy
Update below
I have a table of accounts with a typical acct/parent account architecture to represent a hierarchy of accounts (SQL Server 2012). I created a VIEW using a CTE to hash out the hierarchy, ...
9
votes
4answers
12k views
What is the fastest way to export a table to a text file
I will have a SQL Server 2012 database and a table with 3 million rows and maybe 50 columns. What will be the fastest way for an unattended background .net process (maybe issues some SQL or Powershell ...
9
votes
1answer
238 views
SQL Server 2012 Page Life Expectancy resets to 0 after about 50 days
I've noticed an odd behavior on a 2-server HA cluster and I was hoping someone could confirm my suspicion, or maybe offer some other explanation... Here is my setup:
A 2-server SQL 2012 SP1 ...
9
votes
2answers
421 views
Best way to defrag/compact a database for archival purposes
We've got an SQL Server instance that's used for email archiving (courtesy of a 3rd party archiving package). Every so often, the software is rolled over to a new empty database. We've done this ...
8
votes
2answers
4k views
How To Profile Stored Procedures
I am using SQL Server 2012 and was wondering how to profile stored procedures
For instance can profiler capture each individual SQL statement in a stored procedure, what it is, and how long it takes ...
8
votes
1answer
258 views
Re-run a specific actual query plan
I have captured an actual query plan for a specific query.
After this I've changed a few things around (including updating the statistics) and re-ran that specific query. Now the actual query plan ...
8
votes
2answers
364 views
Shouldn't SQL Server Support RANGE?
As someone who develops websites and does a bit of SQL Server, it seems a no brainer to me that SQL Server should support a simple clause that indicates the results should only include a specific ...
8
votes
5answers
3k views
Freeing Unused Space SQL Server Table
I have a table in SQL Server Express with a lot of unused space.
I need to free up space in the database.
| NAME | ROWS | RESERVED | DATA | INDEX_SIZE | UNUSED |
...
8
votes
3answers
624 views
Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?
The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
8
votes
3answers
449 views
SQL Server query slow when paginated
I am seeing some strange behaviour with the following T-SQL query in SQL Server 2012:
select Id
FROM dbo.Person
WHERE CONTAINS(Name, '"John" AND "Smith"')
order by Name
Executing this query alone ...
8
votes
10answers
6k views
Sql Server Management Studio slow opening new windows
After upgrading both sql server and management studio from 2008 to 2012, the management studio 2012 is very slow when new query windows and dialog boxes are opened. Even right click on tables is slow. ...
8
votes
1answer
186 views
Sequence is reusing
I have a sequence that generates tracking numbers for objects in my system. It had been working fine for quite some time.
Last week we noticed that it was starting to re-use values.
What seems to ...
8
votes
2answers
204 views
Creating index on computed field: string or binary data would be truncated
I have a table Foo with the following fields:
ID bigint not null identity(1,1),
SerializedValue nvarchar(max),
LongValue as TRY_CAST(SerializedValue as bigint)
Now I want to create an index on ...
8
votes
1answer
192 views
Are the cost percentages in this SQL Server plan over 100% for a valid reason?
I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet:
Why are many of the costs listed above 100% ? Shouldn't that be impossible?
8
votes
2answers
2k views
Does a re-index update statistics?
I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is:
Does a re-index update the statistics?
We found discussions online ...
8
votes
1answer
3k views
Upgrade SQL Server 2005 to SQL Server 2012
I have a test environment set up with the following:
Virtual Machine (Hyper-V)
Windows Server 2008 R2 SP1(x64-bit)
Windows SQL Server 2005 Developer Edition SP4 (x64-bit)(default instance name)
1 ...
8
votes
1answer
650 views
SQL Server's READ COMMITTED SNAPSHOT vs SNAPSHOT
I was researching the differences between SQL Server's READ COMMITTED SNAPSHOT and SNAPSHOT isolation levels and came across the following resource:
Choosing Row Versioning-based Isolation Levels
...
7
votes
1answer
543 views
SSIS organization
I'm familiar with SSMS, but I've never used .net, c# or visual studio (been doing other things: Assembler, c, unix, vi, oracle, etc).
I'm trying to figure out basic 2012 SSIS ETL (no BI or data ...
7
votes
3answers
2k views
Can I create a user defined table type and use it in the same transaction?
When I execute the following (in management studio, GO will separate the commands into batches)
use tempdb
begin tran
go
CREATE TYPE dbo.IntIntSet AS TABLE(
Value0 Int NOT NULL,
Value1 Int ...
7
votes
1answer
1k views
Direct the OUTPUT of an UPDATE statement to a local variable
I would like to do this :
DECLARE @Id INT;
UPDATE Logins
SET SomeField = 'some value'
OUTPUT @Id = Id
WHERE EmailAddress = @EmailAddress -- this is a parameter of the sproc
Is this even ...
7
votes
3answers
1k views
SQL Server 2012 Standard Edition - multiple instances and memory utilization
If we have multiple instances of a SQL Server 2012 Standard Edition (which has a 64 GB memory limit) on one server which has 192 GBs of RAM, do both instances only have access to the first 64 GBs of ...
7
votes
2answers
492 views
Can Availability Groups replace transactional replication?
I've been looking at the new features of SQL Server 2012, and how it can help my current situation.
Currently we're on SQL Server 2008 R2, and replicate two OLTP servers' databases to a single ...
7
votes
2answers
413 views
Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?
I'm a fan of surrogate keys. There is a risk my findings are confirmation biased.
Many questions I've seen both here and at http://stackoverflow.com use natural keys instead of surrogate keys based ...
7
votes
1answer
1k views
Can Availability Groups provide seamless failover (with no query failures)?
I have been testing the Availability Groups feature in SQL Server 2012 and am finding that there is approximately 15 seconds of down time when the primary server fails over to the secondary server. ...
7
votes
3answers
1k views
SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc
While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has a LOG_BACKUP for the log_reuse_wait_desc?
SQL Server 2012 SP1. No replication, no mirroring, no log ...
7
votes
1answer
128 views
Query plan cache bloated by ad-hoc queries, even with “Optimize for Ad-hoc Workloads”
I've been noticing what I thought to be unusual issues with our query plan cache, where the plans in the cache were never more than a day old.
Through running the following query (courtesy of ...
7
votes
2answers
2k views
How do Availability Groups work with cheduled jobs and SSIS packages?
I have 2 SQL Servers set up with a database in an AlwaysOn Availability Group.
I also have jobs set up which run against the database. During a failover how do I ensure the jobs will continue to run ...
7
votes
2answers
770 views
SQL Server 2012 Availability Group is it “AlwaysON”?
In a traditional SQL Server clustering when failover occurs, all client that are connected to SQL Server failover instance looses the connection and each client must reestablish a new connection to ...
7
votes
1answer
1k views
Is SQL Server data compression categorically good for read-only databases?
Some literature on SQL Server data compression I read state that the write cost increases to about four times what would normally be required. It also seems to imply that this is the primary downside ...
7
votes
1answer
288 views
Restoring differential backup creates DEFUNCT log file?
Here is my problem. I'm trying to move a database to a new server via a full restore, then cutover with a quick differential backup/restore. I can do a full restore without a problem, but when ...
