All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

9
votes
1answer
2k views

How important are transaction log VLFs for performance?

How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?
9
votes
1answer
5k views

SQL Server Transaction Backups vs Logs

I've inherited a moderately-sized business-critical SQL Server 2008 database and am trying to wrap my head around backup planning. (I'm a developer, not a DBA.) The way our system is set up right now ...
9
votes
1answer
5k views

SSIS 2008 Package Configurations being ignored

With the change to package configurations in 2008 compared to 2005 when I specify /ConfigFile something.dtsConfig on the command line, variables defined in the package are keeping their design-time ...
9
votes
2answers
7k views

Why are non-digits LIKE [0-9]?

My server's default collation is Latin1_General_CI_AS, as determined by this query: SELECT SERVERPROPERTY('Collation') AS Collation; I was surprised to discover that with this collation I can match ...
9
votes
5answers
2k views

Create a plan guide to cache (lazy spool) CTE result

I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't. However, that is sometimes tricky, especially if the ...
9
votes
1answer
960 views

Generating large strings for test data

I was recently trying to create some large strings containing generic test data for a question here. It seems that I used to know of a way to multiply a string. However, I can no longer remember the ...
9
votes
2answers
969 views

A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?

We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box. I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
8
votes
2answers
3k views

How can row estimates be improved in order to reduce chances of spills to tempdb

I notice that when there are spill to tempdb events (causing slow queries) that often the row estimates are way off for a particular join. I've seen spill events occur with merge and hash joins and ...
8
votes
1answer
13k views

Automatic decimal rounding issue

The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals ...
8
votes
3answers
13k views

SQL update satement taking a very long time / high disk usage for hours

Yes it sounds like a very generic issue, but I have not been able to narrow it down very much yet. So I have an UPDATE statement in a sql batch file: UPDATE A SET A.X = B.X FROM A JOIN B ON A.B_ID = ...
8
votes
2answers
2k views

SQL Server - contents of transaction log file in more detail

I have a question regarding transaction log (let’s just call it LDF for short) contents. I am assuming a database with full recovery model. I have read that LDF file contains (logs) each and every ...
8
votes
2answers
1k views

SQL Server 2012 Availability Group is it “AlwaysON”?

In a traditional SQL Server cluster when failover occurs, all clients that are connected to the SQL Server failed instance lose connections and each client must re-establish a new connection to ...
8
votes
2answers
3k views

Why should a CTE start with a semi-colon?

I was just looking at a post on SO (http://stackoverflow.com/questions/12183062/how-to-ensure-contiguity-of-a-tally-table) where Aaron Bertrand proposes using a CTE instead of a numbers table, which ...
8
votes
2answers
3k views

Do indexes consume memory?

I am just starting to learn about memory usage on SQL Server. When using the query in the answer to the question SQL Server 2008 R2 "Ghost Memory"?, I discovered that a single database is ...
8
votes
1answer
293 views

Most efficient way to get stats on an entire SQL Server database

What I'm looking to do is review our databases and lock down any auto-shrink settings, as well as get a handle on which databases/tables are highly fragmented. Is there a particular script I can run ...
8
votes
2answers
59k views

SQL Server shows database in recovery

Today, after a power failure, one database (with Recovery: full) shows "In Recovery" in SSMS. So: myDatabase (In recovery) (database status: recovery, Shutdown) After finish, the "recovery ...
8
votes
4answers
4k views

SQL Server Partitioning - what to use for partition key?

I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued ...
8
votes
4answers
4k views

Are there tools to perform data comparison between two different schemas?

We are migrating our database to a new schema but want to validate the data has been moved correctly. Traditional data comparison tools can compare two databases for differences if the schemas are ...
8
votes
3answers
857 views

Speed impact of using varchar in SQL Server 2008

In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any ...
8
votes
6answers
2k views

What security threats does the SA account and other known account names pose to security?

Does an known account name like sa, pose a security threat to database? When using windows authentication on SQL Server does it impose the same password policy(if it was set to say account lockout ...
7
votes
1answer
142 views

Lead/Lag Implementation in SQL Server 2008 R2: Max memory exceeded

Background I'm trying to establish a "visits" sequence, wherein if an animal is detected at essentially the same place (General_Location) it counts as a single visit, but if it goes elsewhere and ...
7
votes
2answers
3k views

Efficient query for an advanced EAV model

I'm kind of stuck on how to build an effecient query which returns the data of the following EAV structure. Today, a product table already exists, containing 4 fixed fields. We want to upgrade the ...
7
votes
0answers
1k views

SQL Server 2012 performance issues (maybe due to linked server) [closed]

Summary of the problem We've spit our one database server environment into two servers. We are now having intermittent performance issues I am hoping someone can offer clues - perhaps direction ...
7
votes
1answer
17k views

Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. Following up on my previous ...
7
votes
2answers
2k views

TempDB Version Store cleanup

From what I can find the version store will only clean up versions that are older than the oldest active transaction. Question: Is the oldest transaction database specific or will SQL Server keep all ...
7
votes
1answer
1k views

Does Detach/Attach or Offline/Online Clear the Buffer Cache for a Particular Database?

A buddy of mine told me today that instead of bouncing SQL Server, I could simply detach and then re-attach a database and this action would clear the given database's pages and plans from cache. I ...
7
votes
3answers
8k views

Log Shipping - RESTORE WITH STANDBY - on SQL Server 2012 keeps breaking

We are using log shipping and RESTORE WITH STANDBY on SQL Server 2012 in order to restore the database in read-only mode for reporting purposes. However, the log shipping setup keeps breaking after ...
7
votes
2answers
11k views

Restore database from backup file of different version / edition

I read that it's possible to restore a database in SQL Server as long as you're restoring from an older version to a newer version, for backwards compatibility reasons. Does anyone know off hand if ...
7
votes
3answers
2k views

Performance benefit of taking unused databases offline

I have several databases for legacy applications which run in VMs that are currently off until someone needs to look at some history. I have set each of the databases used by these applications to ...
7
votes
2answers
2k views

Conditional compilation of SQL Server stored procedure

Short version: Is there some way to conditionally compile chunks of TSQL code in a SQL Server Data Project using SQL Server Data Tools for Visual Studio 2010? I'm using SQL Server Data Tools in ...
7
votes
1answer
901 views

TempDB Contention with 24 physical tempdb files on 48 core AMD Opteron box

I just received the following notification about an instance of SQL Server 2005. The instance has a core-to-tempdb-file ratio of 2:1 for a total tempdb file count of 24 files. Contention should not ...
6
votes
2answers
165 views

Running sp_AskBrent with @ExpertMode = 1 in PowerShell

I am trying to run sp_AskBrent from PowerShell using Invoke-SQLCmd and capture its output in a variable: $query = "EXEC SAIDBA.monitoring.sp_AskBrent @Seconds=10;" $check = Invoke-Sqlcmd ...
6
votes
4answers
446 views

How can I Debug a Buffer Issue?

I have a production "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)" that is showing weird buffer and page life expectancy (PLE) symptoms. I am running this every minute on my server (to track ...
6
votes
1answer
200 views

Capture and Replay workload

We have a new server which we are migrating too and want to see the potential benefits of the hardware. So the idea is that we capture a workload and replay this against our new box. My question is ...
6
votes
2answers
831 views

What does Page Life Expectancy say about the instance?

I have installed monitoring software on a few SQL Server instances in the environment. I am trying to find bottlenecks and fix some performance issues. I want to find out if some servers need more ...
6
votes
2answers
2k views

Should I check for existence of temp tables in Stored Procedures?

Is there any edge case in which it would be recommended to explicitly check for ,drop and create temp tables at the start of a Stored Procedure instead of just creating them? Similarly, is there a ...
6
votes
4answers
26k views

List table sizes for all tables on all databases

Is there a simple way of listing the size of every table in every database on a MSSQL server? I have used a query on sys.tables to get results for a single database, but we have >100 databases per ...
6
votes
2answers
2k views

problem running xp_cmdshell when Application Name set in connection string

I have an app on 10 or so servers that runs a few xp_cmdshell statements against MSSQL 2008. It works fine on all the servers except for one. To make things worse, I can run all the commands in SQL ...
6
votes
2answers
140 views

RID vs INCLUDE on a large field

I have a table that stores notes create tblNote( Id int identity(1,1), ParentId int , ParentType varchar(32), NoteType varchar(32), Note varchar(max), CreatedBy ...
6
votes
2answers
5k views

SQL Server 2008 can't repair consistency

I have a problem with a SQL Server 2008 database. Launching DBCC CHECKDB I get this error: SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: ...
6
votes
1answer
2k views

need suggestions to improve view performance

I am looking to improve the performance of a view in SQL Server 2008. This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these ...
6
votes
2answers
8k views

Deny access to information schema in SQL Server

I am looking for the best way to disable access to the sys.tables / Information Schema for a user / group in SQL Server. I found this thread from 2008 It shows a way how to deny access on ...
6
votes
4answers
21k views

Could not find the Server Agent in SSMS

I am following the guide - http://www.sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/ which is working until I reach Step 8 8. Now to schedule your newly created script. ...
6
votes
1answer
1k 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 ...
6
votes
1answer
1k views

Service Broker & AlwaysOn Availability Groups: Odd Transmission Queue Behavior

I have also posted this question on my blog: http://www.sqldiablo.com/2012/04/15/service-broker-alwayson-availability-groups-odd-transmission-queue-behavior/. I’ve been working on a project over the ...
6
votes
1answer
3k views

Why does it take so long to calculate an Execution Plan?

One of our customers has just upgraded to a new server. For a particular stored procedure the first time you execute it, is taking over three minutes to run. Subsequent runs are less than 1 second. ...
6
votes
2answers
2k views

SQL Server Partitioning vs Indexes on Separate Filegroups

I am just about to start on an excercise to reorganise a partitioned table and I was looking for best practises or recommendations. Let us say I have a billion row table and 8 logical disks available ...
6
votes
1answer
9k views

Windows Authentication for a non-local non-domain Login

I have SQL Server 2008 R2 installed on a Windows Server 2008 R2 box. There is no domain in the network. Is there any way for me to add a Windows Login on SQL Server for a non-local and non-domain ...
6
votes
2answers
3k views

How do I clear a table from SQL Server's cache?

I have a few tables in my database that should not be cached. How do I tell SQL Server not to cache a table's pages or how do I flush a single table from the cache? Flushing all the cache is not an ...
6
votes
2answers
230 views

SQL Server Internal Inconsistency on GROUP BY

I recently recovered a database from a hard-drive failure using DBCC CHECKDB WITH REPAIR ALOW DATA LOSS. Now I can select records from a table-valued function to retrieve sales records, but using a ...