This tag is specifically for SQL Server 2012
25
votes
2answers
2k 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 ...
24
votes
4answers
7k 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 ...
19
votes
2answers
1k views
Why are Denali sequences supposed to perform better than identity columns?
In his answer to sql2005 whats better: identity columns or generated uinique id values?
mrdenny mentions, says
When SQL Denali comes out it will
support sequences which will be more
efficient ...
14
votes
6answers
1k views
What is SQL Server “Denali”? What's new?
I found a new title called SQL Server "Denali" in the drop down list on MSDN, but I didn't find much information about it:
What's New (this documentation is for preview only as I see)
Top 9 New ...
14
votes
1answer
897 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 ...
13
votes
2answers
650 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 ...
13
votes
2answers
550 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 ...
11
votes
1answer
200 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 ...
10
votes
1answer
223 views
MDX Query for RANK regardless of hierarchy
I'm hoping to add a calculation to my SSAS Cube (SSAS 2012) that will show the relative rank of whatever the current member is against all other members in that grouper, without specifying the ...
9
votes
4answers
1k 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 ...
9
votes
1answer
688 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 ...
9
votes
2answers
216 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
1answer
310 views
Forcing an index spool
I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear.
However, I end up with a ...
8
votes
3answers
162 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 ...
8
votes
2answers
312 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
3answers
156 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 ...
7
votes
2answers
461 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 ...
7
votes
2answers
1k views
SSMS Tools Pack Alternative
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 ...
7
votes
1answer
393 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
277 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 ...
7
votes
1answer
135 views
Dynamic Dimension Security
I have a cube in SSAS 2012 with about 30 dimensions, one of which is Company.
The Company dimension contains a list of companies, of which there are several hundred in the cube. Our security model ...
7
votes
1answer
211 views
selecting more fields causes catastrophic failure
I have a connection to a linked server from sql server 2012 to pervasive sql.
When I do select field1, field2, field3 from mytable everything works!
However, when I do select field1, field2, field3, ...
7
votes
2answers
3k views
Downgrading SQL Server 2012 to Lower Edition
I have a Win2008 R2 server with an enterprise edition of SQL Server 2012 installed. Because my production server is going to be standard edition, is there a way to downgrade editions from enterprise ...
6
votes
2answers
4k views
Sql Server 2012 data warehousing and different versions
With Sql Server 2012 there are 3 flagship editions: Enterprise Edition, Business Intelligence, Standard.
The full comparison between the three: ...
6
votes
3answers
382 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 ...
6
votes
3answers
221 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.
6
votes
1answer
134 views
How do you move a SQL Server 7 database to SQL Server 2012?
I would like to move a database from SQL Server 7.0 to SQL Server 2012. They are not compatible for a backup and restore. Is there a way to export from SQL Server 7.0 and import it to 2012? I only ...
6
votes
2answers
133 views
Does the order of columns in a columnstore index matter?
I have a table with ~200 million rows and ~15 columns in it. I am planning to create a COLUMNSTORE index on my table.
Will there be any change in performance based on the order of columns that I use ...
6
votes
2answers
126 views
How to flatten results of a table with two related “many” tables?
I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them.
I have the following tables (somewhat abbreviated ...
6
votes
1answer
169 views
DBCC TRACEON (652) does not disable read ahead reads
Refering to this StackOverflow question: http://stackoverflow.com/questions/13307485/sql-server-clearing-cache-has-no-effect-on-io-stats
I used the trace flag 652 which had, at the time disabled the ...
6
votes
1answer
3k views
Is SQL Server 2012 Management Studio “Express” any different?
Are there any differences from the version of SSMS that comes as part of the SQL Server 2012 Enterprise Installer and the SQL Server 2012 Express Management Studio?
6
votes
2answers
686 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 ...
6
votes
2answers
334 views
How might a corrupt partition in TempDB result in DBCC CHECKDB reporting no issue?
One of our SQL Servers reported the following error recently:
DATE/TIME: 2/25/2013 9:15:14 PM
DESCRIPTION: No catalog entry found for partition ID 9079262474267394048
in database 2. The ...
6
votes
4answers
1k 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 ...
6
votes
1answer
573 views
Can the SQL Server 2012 Always On feature provide seamless failover (with no query failures)?
I have been testing the the Always On high availability 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 ...
6
votes
2answers
167 views
Few Questions about SSAS Cubes
I am newbie to SSAS 2012 and here are my questions:
Can we have measures of complex types (table)?
Is it possible to order the results by a specific measure or attribute?
My cube can be modeled as ...
6
votes
1answer
185 views
Recovery time effects of AlwaysOn
We did different kinds of basic Tests and AlwaysOn passed in many tests. We finally did a heavy write test for AlwaysOn and it gave surprising results.
The actual Test details are here, the goal is ...
6
votes
1answer
99 views
Ok to drop indexes on FK's if they have no stats in the DMVS
I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes.
Are you 100% safe to drop these ...
6
votes
2answers
391 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 ...
6
votes
2answers
732 views
sp_executesql with user defined table type not behaving correctly
Problem
Is there a known issue with user defined table types as parameters to sp_executesql? Answer - no, I'm an idiot.
Set up script
This script creates one each of table, procedure and user ...
6
votes
1answer
684 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 ...
6
votes
7answers
2k 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. ...
6
votes
1answer
98 views
LATCH_EX Waits on Resource METADATA_SEQUENCE_GENERATOR
We have a process that generates an inventory report. On the client side, the process splits of a configurable number of worker threads to build a chunk of data for the report that corresponds to one ...
6
votes
1answer
214 views
Warning in query plan “Cardinality Estimate”
create table T(ID int identity primary key)
insert into T default values
insert into T default values
go
select cast(ID as varchar(10)) as ID
from T
where ID = 1
The query above has a warning in ...
5
votes
1answer
551 views
Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?
I posed a question about Denali sequences here and another question about emulation of identity columns in Oracle Global Tables here.
I always thought that I could trust that identity values ...
5
votes
2answers
2k views
Can I connect to a SQL Server 2000 instance from SQL Server Management Studio 2012?
I would like to update my development machine to SQL Server 2012 but I still manage some (very old) SQL Server 2000 machines. Wil my 2012 SSMS be able to connect to those SQL Server 2000 machines?
...
5
votes
2answers
117 views
How best to maintain SQL log file sizes
I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery.
Right now, I'm ...
5
votes
2answers
309 views
How to handle sql server database logins when using AlwaysOn. (they aren't syncing)
We have 2 servers in an AlwaysOn group.
While the user accounts within each syncronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie ...
5
votes
1answer
353 views
SQL Server 2012 Resource Governor Enhancement
In SQL Server 2012 what specific enhacement was made regarding controlling memory usage by using Resource Governor. Up until SQL Server 2008 R2 resource governor was only able to control memory ...
5
votes
1answer
133 views
How does SQL Server generate a query execution plan that adds up to 6,000%? [duplicate]
Today I was on The Heap and was looking at a query plan for something I thought could be improved. However, it created something that shook my belief in the SQL Server query optimizer. Can I still ...
