Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

10
votes
3answers
4k views

SQL Server - Temporary vs. Physical Tables

A movement is afoot in my place of employ to move away from using #temp tables and instead to use permanent physical tables with SPIDs. Whenever one would have previously INSERTed INTO a #temp table, ...
10
votes
5answers
3k 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
1answer
836 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
2answers
5k views

Need to understand parallel query execution error

Today we experienced a degradation in performance on our production sql server. Durring the time this occurred we logged several "The query processor could not start the necessary thread resources ...
9
votes
1answer
289 views

DB_ID context from farther up call stack

In SQL Server, is it possible to get the DB_ID from the context from farther up the call stack? My goal is to create some handy (and admittedly hacky) utility functions in a dev sandbox database ...
9
votes
3answers
753 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 ...
9
votes
3answers
1k views

How to control the order of servers listed in the SSMS connection dialog?

My current project involves a lot of database shuffling as we upgrade from SQL 2008 -> 2012, re-task hardware, repeat. One minor annoyance is the connection dialog in SSMS re-shuffling the server ...
9
votes
4answers
3k views

Sort order specified in primary key, yet sorting is executed on SELECT

I'm storing sensor data in a table SensorValues. The table and primary key is as follows: CREATE TABLE [dbo].[SensorValues]( [DeviceId] [int] NOT NULL, [SensorId] [int] NOT NULL, [SensorValue] ...
9
votes
2answers
1k views

Tracking stored procedure usage

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
9
votes
1answer
2k views

Why is Clustered Index Scan Number of Executions so high?

I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time. The only difference between ...
9
votes
3answers
359 views

Are heaps considered an index structure or are they strictly a table structure without index?

Inspired by this post: https://twitter.com/#!/SQLChicken/status/102930436795285505 Heaps: Are they considered an index structure or are they strictly a table structure without index?
9
votes
5answers
3k views

When should nonclustered indexes be stored on separate filegroups?

I have heard that storing indexes on a different filegroup and drive increases performance in a database because the drive doesn't have to go back and forth between the index and the data to which the ...
9
votes
2answers
721 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
1answer
262 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
1answer
568 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 ...
8
votes
5answers
3k 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
819 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 ...
7
votes
1answer
660 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
1answer
344 views

Strange behaviour DBCC Shrinkfile

I am attempting to run a dbcc shrinkfile in chunks of 1GB against a database where 95% of the data has been archived and deleted. I'm leaft with a 235GB file where 9GB is data/indexes. I want to ...
7
votes
1answer
2k 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 ...
7
votes
1answer
636 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 ...
7
votes
2answers
870 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
3answers
4k views

Performance Hit using CAST() in TSQL (SQL Server)?

We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField). So, if myField is of type NVARCHAR, we ...
7
votes
6answers
1k 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 ...
6
votes
1answer
717 views

Database design for handling 1 billion rows and counting

We receive real-time GPS data at a rate of around 5000 pr. minute (from 4 TCP servers). Each server uses a single connection to insert the data, and buffers data in between inserts. Every 15 minutes ...
6
votes
2answers
1k 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 ...
6
votes
2answers
791 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
123 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
31k views

Listing the existing SQL Server Logins and Users

I know we can check the logins and the users that are defined, using GUI in SQL Server, but am wondering how we can do this check using script. I ran the query below but it shows Principal_id which ...
6
votes
2answers
222 views

Automation of backups of Large Number of Database

In a multi-tenant web application where a database is set up for each tenant, the database can be backed up and restored manually through a fairly conventional approach. I am considering this type of ...
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
2answers
215 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 ...
6
votes
4answers
714 views

How can I tell if my SQL Server DB performance is hardware-limited?

Testing an app currently under single-user load - as the test data has increased to production sizes (400k-2M rows per table), some SELECT sp's are not quite fast enough anymore (with limited test ...
6
votes
6answers
2k views

SQL Server 2005 Huge DB issue

My DB files are growing very fast ( designer is not me and just a weekly report makes 7 GB growth ). And finally, I don't have enough space in the disk. And number of un-reported weeks counting up. ...
5
votes
2answers
686 views

An efficient way to compare two large data sets in SQL

Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations. The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and ...
5
votes
0answers
603 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 ...
5
votes
2answers
1k views

How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks?

I am having a (rather standard) daily maintenance plan created with SQL Server Management Studio Maintenance Plan Wizard. AFAIU, the penultimate task "Back Up Database Task" will be executed ...
5
votes
1answer
2k views

SQL Server - How to determine ideal memory for instance?

We have some virtual machines that have X memory allocated to them. This amount of memory is somewhat random. It was allocated to the machine because that was the amount of memory the physical ...
5
votes
2answers
187 views

'Cleanse' a SQL Server database file created with Instant File Initialization enabled?

I have Instant File Initialization enabled on our SQL Server, so the 'empty' part of the database file is not zeroed before being allocated. My understanding is that this means that the file could ...
5
votes
2answers
2k views

What are Measures and Dimensions in Cubes

I'm very new to Microsoft Sql Server Business Intelligence and Analysis Service(but I'm programming for years with SQL Server).Can any one describe Measures and Dimensions in Cubes in Simple words(If ...
5
votes
3answers
5k 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 = ...
5
votes
1answer
2k views

How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”?

We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day. The problem is, the differential backups don't always seem to ...
5
votes
4answers
8k 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. ...
5
votes
1answer
2k views

Allow user to do anything within his own schema but not create or drop the schema itself

I have created a schema in SQL Azure and granted the following permissions to a database role: CREATE ROLE myrole AUTHORIZATION dbo; EXEC sp_addrolemember 'myrole', 'myuser'; CREATE SCHEMA myschema ...
5
votes
1answer
786 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
2answers
370 views

Can I prove my database does not have memory issues?

(This is a continuation of do indexes consume memory) A fellow database developer believes we are having memory issues. He has seen increased run times in some standard queries - going from under 10 ...
5
votes
2answers
3k views

Data type for storing an array of flags (a bitmap/bit array)

I need to store a bit array for each record of a table, supporting the following operations: Testing if a bit is set, and setting a bit (using SQL) Querying and setting the value using ADO 2.8 (not ...
5
votes
3answers
816 views

How to store many smallish time-series in a relational DB?

Input Data Multiple Tests Beds generate measurement data of various complexity. In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
5
votes
3answers
651 views

Using SQL Server on separate networks

I have a bunch of SQL Servers installed on Windows2008R2. Currently, each windows server has two NIC's, each connecting to separate networks. In other words, each server appears on each network. ...
5
votes
1answer
2k views

Send email when certain Data is being inserted in MS SQL Server

I realize that there is an integrated email notification system in SQLServer, where I can configure it to send an email using my organization's Exchange server. I want to use that to send email on ...