This tag is for SQL Server 2012, major build version 11.00.xxxx.

learn more… | top users | synonyms (3)

9
votes
10answers
10k 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. ...
9
votes
1answer
2k views

Using SQL Server 2012 Standard with Bitlocker

We're trying to use Bitlocker to secure an SQL Server 2012 database. We don't have any issue getting bitlocker to work... the issue we have is that once the drive is locked, SQL Server can no longer ...
9
votes
3answers
459 views

What is a deterministic method for evaluating a sensible buffer pool size?

I'm trying to come up with a sane way to understand if the max server memory (mb) setting is appropriate (either should be lower, or higher, or stay the way it is). I am aware that max server memory ...
9
votes
1answer
5k 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 ...
9
votes
2answers
512 views

Improving the performance of STIntersects

Table T_PIN has 300,000 pins and T_POLYGON has 36,000 polygons. T_PIN has this index: CREATE SPATIAL INDEX [T_PIN_COORD] ON [dbo].[T_PIN] ( [Coord] )USING GEOGRAPHY_GRID WITH (GRIDS =(LEVEL_1 = ...
9
votes
2answers
3k 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 ...
9
votes
2answers
553 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
7k 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: ...
8
votes
3answers
5k views

Truncated 200GB table but disk space not released

I have only 2GB left, so I need to remove this history table. This table now is empty but the database disk space not released. And the database file is 320GB.
8
votes
2answers
235 views

Are “out of the row” fields read when a clustered index is used?

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row - the data row will have a pointer to another location where the 'large value' is stored. I have the ...
8
votes
1answer
287 views

What is the different between compression on the PK vs. on the Table?

Data compression can be set on the table: CREATE TABLE dbo.SomeTable( SomeId [bigint] NOT NULL, OtherId [bigint] NOT NULL, IsActive [bit] NOT NULL, CONSTRAINT [PK_Some] PRIMARY KEY ...
8
votes
3answers
5k 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 ...
8
votes
1answer
456 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
1k views

In SSIS 2012, what is the purpose of flagging a package as an “Entry Point” package

In the Visual Studio designer you can right click on a SSIS package and designate it as an "Entry Point" package" Doing a search I found this page on MSDN which states: The value of 1 signifies ...
8
votes
3answers
140 views

IF statement not skipping TempDB when Looping through Databases with sp_MSForEachDB

[SQL Server 2012 SP2 EE] Why does the following script gives me an error relating to tempdb? exec sp_MSForEachDB ' IF ( (select database_id from sys.databases where name = ''?'') > 4) ...
8
votes
2answers
370 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
187 views

Configure RAM for SQL Server and NUMA

Accidental DB admin here. Question: Would you still manually limit max RAM for SQL Server even though the sole purpose of that server is to serve SQL Server database engine only? I have Microsoft ...
8
votes
5answers
9k 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
981 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
2answers
428 views

SQL Server : how to create user with access only from specific HOSTNAME or IP addres

I have one stupid app which I rely on, which has hardcoded connection string inside. To increase security of my SQL Server I'd love to make SQL users same as one hardcoded inside app, but I want to ...
8
votes
1answer
2k 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. ...
8
votes
1answer
11k views

Detecting the locked table or row in SQL Server

I'm trying to understand/learn how to track down the details of a blocked session. So I created the following setup: create table foo (id integer not null primary key, some_data varchar(20)); ...
8
votes
1answer
553 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
2k 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
321 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
3answers
13k views

Upgrade process from SQL Server 2000 to SQL Server 2012

I have a SQL Server 2000 database that I would like to upgrade to SQL Server 2012 (running on a different server). I don't think there is a direct upgrade path, but if there is, what is it? My ...
8
votes
2answers
93 views

SARG cardinality estimate, why not full-scan?

Why there is no full-scan (On SQL 2008 R2 and 2012)? Test data: DROP TABLE dbo.TestTable GO CREATE TABLE dbo.TestTable ( TestTableID INT IDENTITY PRIMARY KEY, VeryRandomText VarChar(50), ...
8
votes
2answers
119 views

Getting a realistic query plan when partitioning with low volumes of data

We're using partitioning to reduce the amount of blocking our OLTP system experiences due to locks, with a partition scheme that splits the working tables into 100 partitions based on a customer id. ...
8
votes
1answer
1k views

SSIS 2012 Create environment variable fails

I'm working a script to port an Environment from one server to another. I am running into an issue calling catalog.create_environment_variable wherein I get the error "The data type of the input value ...
8
votes
1answer
680 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 ...
8
votes
1answer
2k 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 ...
8
votes
2answers
1k views

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
8
votes
3answers
8k views

Best practice for tempdb log file

I have read many blogs here and there about how to configure tempdb data files but i didnt found any information regarding the tempdb log file. Here's the strategy that I'm presently using with my ...
8
votes
2answers
4k views

SQL Server database design for “archived but available” data

We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice. ...
8
votes
1answer
231 views

High PAGELATCH_* and WRITELOG waits. Are they related?

We are seeing very high PAGELATCH_EX and PAGELATCH_SH wait types along with high WRITELOG waits. I've diagnosed the query causing the PAGELATCH waits and can eliminate them by reducing the insertion ...
8
votes
0answers
291 views

Troubleshooting THREADPOOL Waits [closed]

From everything I have read, THREADPOOL waits occur when a task is waiting for a worker thread. A high amount of THREADPOOL waits typically means you are running out of available worker threads. ...
7
votes
2answers
3k views

Is there a better option than Union All for multiple selects from the same row?

Example Schema: CREATE TABLE [dbo].[Base]( [ID] [int] IDENTITY(1,1) NOT NULL, [Option1ID] [int] NULL, [Option2ID] [int] NULL, [Option3ID] [int] NULL, [Option1Name] [varchar] NULL, [Option2Name] ...
7
votes
3answers
7k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
7
votes
1answer
870 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
2answers
2k views

Securely generate a UNIQUEIDENTIFIER in SQL Server

I intend to be using a UNIQUEIDENTIFIER as an access key that users can use to access certain data. The key will act as a password in that sense. I need to generate multiple such identifiers as part ...
7
votes
2answers
653 views

How can I reliably determine if a given SQL Server instance has the “Lock Pages in Memory” right?

The "Lock Pages in Memory" right can be granted to the service account used by SQL Server. This allows SQL Server to prevent memory being paged to disk. I've noticed several of our SQL Server ...
7
votes
1answer
900 views

Why 0 is equal to empty string?

I need some help in finding why the following T-SQL statement returns 1 (true): SELECT IIF( 0 = '', 1, 0) I guess someone has change an ANSI option like SET ANSI_NULLS or something else that is ...
7
votes
3answers
11k views

Visio 2010 and SQL 2012 - Reverse Enginnering

We have upgraded to SQL 2012 and Am trying to reverse engineer our Dbs into Visio 2010. I setup a connecting using SQL 11 driver and Visio complains that that driver is not supported. Any one run ...
7
votes
3answers
2k 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
806 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
1answer
2k 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
2answers
1k views

What effect will reducing the size of a varchar column have on the database file?

We have a number of tables in our database that have VARCHAR(MAX) columns where a VARCHAR(500) (or something much smaller than max) will suffice. Naturally I want to clean these up, and get the sizes ...
7
votes
3answers
2k views

Get transactions that failed or were never committed

Is there anywhere I can look to find failed transactions in SQL server 2012?
7
votes
1answer
173 views

How does SQL Server's optimizer estimate the number of rows in a joined table?

I am running this query in the AdventureWorks2012 database: SELECT s.SalesOrderID, d.CarrierTrackingNumber, d.ProductID, d.OrderQty FROM Sales.SalesOrderHeader s JOIN ...
7
votes
2answers
27k views

Installing SQL Server 2012 Express fails with error “ Could not find the Database Engine startup handle.”

I'm attempting to install SQL Server Express 2012 w/ SP1 x64 on Windows 7 SP1 x64. This machine has previously had SQL Server Express 2012 installed. This instance was upgraded to SQL Server 2012 ...