In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Data Generator How do you generate test data for your database?
SQL Data Generator quickly populates databases with intelligent and meaningful test data. "SQL Data Generator is simple and effective." Michael Gaertner, Quintech. Download a free trial now.
 
SQL DBA Bundle ‘Six Scary SQL Surprises’
Brent Ozar joins the DBA Team, for Lesson 3 of their ‘Top 5 Hard-earned Lessons’. Gain valuable tips from the pros - Read now.
 
Pass The world of data is changing – are you ready?
Upgrade your skills at the PASS Business Analytics Conference – use BACSSC to save $250 today!

In This Issue

An Orders of Magnitude Problem (T-SQL Tuning)

When a query suddenly starts taking much longer, what do you do? This story from Jerry  More »


Getting Started with Extended Events in SQL Server 2012

Extended Events provide a way of unintrusively monitoring what's going on in a SQL Server instance. Unlike SQL Server Profiler and SQL Trace, it has little performance impact. Now, in SQL Server 2012 SSMS, it is relatively easy to use, as Robert Sheldon shows. More »


Help Guide the Future of SQL Compare

How changes are applied to databases differs from person to person and from organization to organization. Here at Red Gate we’re writing the next chapter for SQL Compare and we need your feedback to validate some of our ideas. To help us in this endeavour, and to enter a prize draw for one of five $50 Amazon vouchers, please complete the following survey. More »


From the SQLServerCentral Blogs - Cleaning orphaned SIDs after a SQL Server VM template deployment

A few weeks ago (or longer, I’m behind on email again) Nate Palm (LinkedIn) messaged me after the most recent... More »


Editorial - Resetting DMVs

Recently I was working on an indexing presentation and looking for missing, duplicate, and unused indexes. As I set up demos to show the effects of indexing, I was constantly adding and deleting objects, and even resetting the DMVs to show statistics by starting the instance. The documentation for some DMVs, like sys.dm_db_index_usage_stats include a note that the counters are initialized to zero when the instance is restarted. If a database is taken offline, or detached, all rows referencing the database are removed.

That got me thinking. Why is this data removed? I'm sure some of the data is stored in memory and automatically reset, but is this the best way to handle this data? Wouldn't it be better to persist this data and allow the DBA to reset values when they were ready? I know we can store this data in a table periodically now, but I think keeping this inside the system views would make sense as an option in SQL Server. When we shut down a database, persist this data inside the database. That would be closer to my vision of self-describing databases, which contain all their own metadata.

I know there might be performance impacts to persisting this information, but I can also see benefits, especially when you might have system crashes or problems. Being able to recover the information from DMVs, even incomplete information, brings us closer to a robust, flight-recorder kind of system that monitors itself and allows administrators to review information that might help them discover the causes of any issues.

I don't think that every piece of data needs to be guaranteed to be written to the DMVs, or that the system should slow down to do so, but give the DBAs some control here. If we have the need for more extensive logging, or we want to retain whatever information we can, allow it to persist in the DMVs, or at least make snapshots of DMV data easy to take and store. It's another form of logging that I'd appreciate having available from the platform.

» Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

» To submit an article, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

The following statement is used to create a temporary table:

Select
       1 As [RowId]
     , 'True or False: The HAVING clause is the 4th phase of logical query processing?' As [Question]
     , 'True: 1-FROM, 2-WHERE, 3-GROUP BY, 4-HAVING, 5-SELECT, 6-ORDER BY.' As [Answer]
     , '15 Jan 2013' As [PublishDate]
     , {d '2012-11-15'} As [SubmitDate]
  Into
       #TempQoD;

What will the data types be for the PublishDate and SubmitDate columns?

Note: This has only been tested in SQL Server 2008 SP3 with the SQL_Latin1_General_CP1_CI_AS collation.

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: T-SQL. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.


Yesterday's Question of the Day

What will be the output of the query?

CREATE TABLE #TEST 
(
 FirstDate DATETIME,
 LastDate DATETIME2
)

DECLARE @i int

SET @i = 100

WHILE @i > 0
 BEGIN
  INSERT #TEST values (sysdatetime(), sysdatetime())
  SET @i = @i - 1 
 END

SELECT distinct a.FirstDate, b.LastDate
 FROM #TEST a
  INNER JOIN #TEST b
    on a.FirstDate = b.LastDate

Answer: No rows will return

Explanation: No rows will get returned as the SYSDATETIME() gets rounded up/down for DATETIME datatype. GETDATE() is suitable for DATETIME datatype

Links to follow:
http://msdn.microsoft.com/en-us/library/ms187819.aspx
http://msdn.microsoft.com/en-us/library/bb630353.aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Featured Script

SQL Server Backup, Integrity Check, Index and Statistics Maintenance

Solution for Backup, Integrity Check, Index and Statistics Maintenance in SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. More »


Database Pros Who Need Your Help!

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

4 database instances in a single server (SQL Server 2005) - Hi Guys, I have 4 database instances (2005) running in a single server. How am i able to know how much memory...

32,000 records - simple SELECT is slow - I have "equity_ids" table (32 columns, all varchar(16)) with no Primary Key (SQL 2005) Just 3 Non-clustered indexes on varchar fields. [SELECT...

Is trigger a bad solution - Dear, I fire triggers on different events. But some of my clients insist on not using trigger. Because they claim that...

My MSDB Database is over 25 GB on production server - The following tables are consuming alot of space in the msdb database. LakeSideLockLogger._LakeSide_DbTools_LockLog-------------------------14.5 GIG LakeSideLockLogger._LakeSide_DbTools_LockExecStack-------------------7.5 GIG LakeSideLockLogger._LakeSide_DbTools_DeadlockLog---------------------0.4 GIG LakeSideWaitsLogger._LakeSide_DbTools_WaitsLogger_WaitsDBLog--------0.3 GIG Please hel

backup job error - Team, My backup job was running continuosly stating with error as BackupMedium::ReportIoError: write failure on backup device 'SQLBACKUP_F2381D64-154C-4F22-8B67-ED444941C3AF01'. Operating system error...

SQL Server 2005 : Business Intelligence

ssis doubts - hi friends i have small doubt in ssis package.plese tell me how to solve issuse. text file source contains data like id...

SQL Server 2005 : Data Corruption

Help Please - DBCC Check Failed-The operating system returned error 87 - Hi folks came in today and found this error on one of my databases. Would appreciate any assistance on this as I...

SQL Server 2005 : SQL Server 2005 Performance Tuning

sql server 2005 (std edition 32 bits) on windows server 2003 enterprise 32 bits - Hi Guys, I have 8GB RAM on windows server 2003 enterprise 32 bits. From my computer, right click properties I can...

Can Someone Explain These Wait Times to Me? - So I know what wait times are, the amount of time SQL spends waiting on a resource. What I don't...

High Index Count/Space versus High CPU and Logical/Physical Reads - We are working on trying to reduce the high number of IO waits on one of our production servers, a...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS Flat File Connection Manager - I am trying to automate the move of data in .txt files to staging tables in Sql. I have been...

Lookin for Reference for EDI Using SSIS and X12 Format Files - Let me start by saying that I'm not looking for assistance in solving a work problem. I'm looking to improve...

SSIS & Environment Variables - I've been struggling w/ some quirky issues lately regarding use of environment variables with package configurations in my SSIS packages. When...

SQL Server 2005 : T-SQL (SS2K5)

UPDATE PART OF DATA - TABLE IS LIKE COMCOD ACTCODE ACTDESC 3306 180001 ADVANCE TO STAFF 3306 180002 ADVANCE TO OTHERS 3306 180003 ADVANCE TO SITE OFFICE I LIKE...

MULTIPLE INSERT IN A DESIRED SEQUENCE - I HAVE A TABLE WITH THREE FIELD COMCOD nchar(4) DUPLICATE ALLOWED FOREIGN KEY, ACTCODE nvarchar(6) PRIMARY KEY, ACTDESC nvarchar(250). TABLE IS...

Looping without cursor not updating properly - I got asked to not use a cursor for a loop that I'm working on so I grabbed the code...

SQL Server 2005 : SQL Server Newbies

Problem loading csv files with SSIS - I receive a number of csv files from a third party on a regular basis, it could be 1 it...

scheduling job - Hi friends, We need to monitor a user session for locks to troubleshoot a problem in the program. When the...

SQL Server 7,2000 : General

SQL 2000 - The selected package cannot be opened - The DTS Designer has been closed - I have two SQL 2000 servers, Db1A and Db1B. There has been NO SQL 2005/2008 is or has ever been...

SQL Server 2008 : SQL Server 2008 - General

SQL Server RAM usage - Hi Everyone, Our production SQL Server has 8GB RAM, we have noticed that the Server is consistently using 7.80GB. The CPU...

Query runs too slow! - I have the following query which takes around 15 minutes to run: dbo.All_keys_ = 88 million rows dbo.Load1_keys = 900 thousand rows [code="sql"] CREATE TABLE dbo.Load1_PotentialOverlaps_(Master_Id...

Open query limitation - Is there any workaround for the 8000 character limitation in the openquery for executing MDX queries? is there any other option...

Ways to replicate - Hello, I have a live db that users need to access it for reporting purposes. What is the best way...

package execution completed. click here to switch to design mode - hi all, when i run my ssis package after 5-10 sec in the bottom of the screen it's showing "package...

Encryption doubts - I want to encrypt my database. So which method is simple and the best. If i use symmetric key then...

inserting data from one table to another and want to create primary key at same time - I am creating a table by using an insert into from another table then I am deleting any duplicate rows from...

How to view stats used in query? - Hello all, I could use some help trying to better understand how stats are used in this situation. I have a query...

maximum rows without duplication - Consider this table: declare @a table (a int,b int,c char(1)) insert into @a select 1,17,'a' union all select 1,15,'b' union all select 2,15,'c' union all select...

Converting datetime format - I have a following table: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Tbl1]( [Old_Date] [datetime] NULL, [New_Date] [datetime] NULL ) ON [PRIMARY] INSERT INTO TBL1(Old_Date)values('2012-12-31') INSERT INTO...

Getting the files stored in a database table? - Hi All, I am struggling to find a solution / guidelines / 3rd party or internal database tools that allows me to extract...

Permissions for DTEXEC - Any suggestions are welcome. I am calling DTEXEC like this in the context of database A. '"E:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe Mypackage"'...

Index confusion - Hi everyone. I have a table with a few million rows in it. There is a process as part of...

Is this is the Best Practice to select E: for the SQL Server root directory? - Hi Experts, Is this is the Best Practice to select E: or any other driver(not C:) for the SQL Server root...

T-SQL Code Review: Looping Record Deletion w. Date Validation - Hi there, I put this script together based off of some code I found in a few other threads on...

To not RBAR - I have a table of taxonomic entries. It is for paleontology, where the entire taxonomic structure is often not known....

Find month totals between date columns - I have a sample view with some dates. How would you find the numbers of items open per month. Say...

OUTPUT variables in Profiler - The following is what SQL Profiler shows for a stored procedure call: declare @p1 int set @p1=20611159 exec dbo.Set_Phone_6 @p1 output,NULL,NULL,NULL,NULL select @p1 go In the...

Looging all executed SQL Server Queries - Hello Friends, I am using SQL 2008 R2. This instance of SQL Server ,many user uses.All user uses the same user sa...

Larger Date Range taking MUCH less time - Hi I'm executing the below TSQL on a table with about 700,000 records. I am clearing the caching so it does...

Differential Backup Blocking Trans Log Backup - Hi, I have a maintenance plan that runs a differential backup every 4 hours and a separate job that backs up...

my backup maintenance plan is failing - Hi friends my backup plan is failing from error logs i got this information. can any one throw some light...

Doubt in Data Warehouse design... - Hi, We are creating a Data Warehouse for our Client We need to store a list of locations that our company. My Colleague...

Create CLUSTERED Index - Hi I want to change the order of two fields in Pk of a table,I need to drop it and recreate...

Diagnosing Page Latch Issue - I'm working with both a Vendor's DB and a purge script they've provided, and I need some outside opinions. This purge...

Backup issue through maintenance plan - i am getting the below error while taking the backup via maintence plan.. i am getting an error only for...

Sharepoint SQL Server - http://technet.microsoft.com/en-us/library/cc298801.aspx •To ensure optimal performance, we strongly recommend that you set max degree of parallelism (MAXDOP) to 1 SQL Server instances...

Query optimization - Hello friends, I have table contains millions of records. When i fired query to get record by id. Query get millions of...

Inserting currency symbols in money datatypes - Hi, I heard we can insert currency Symbols like $ to Money and small money datatypes. But while i am trying i...

Attempt to fetch logical page (1:440) in database 2 failed. It belongs to allocation unit 422212869292032 not to 6269010747738816512. - hi all, One of my sp is giving the following error some times not every time: [b]Attempt to fetch logical page (1:440)...

SSRS Reporting assitance- consolidated report using 3 Stroed PROC - Hi, I have 3 Stored Procedures 1) SP_JobPostingID, 2)SP_WorkerOrder, and 3) SP_WorkOrderRevision Note- all the 3 have same number and signature of Columns, what...

query performance - I have a table with 6 million rows. The table has around 200 columns. The data can be sliced and...

sp_updatestats and the default sampling rate - Using SQL Server 2008R2 As my Production database approaching 500GB and potentially can grow up to 1TB in 2 years, issue...

Read & Write Permissions on SQL Server Agent Service startup account - How do I add read permission and write permission to the Temp directory of the SQL Server Agent Service startup...

Parameter in sp_delete_jobsteplog don't work - Hi, in our organisation we normally set up maintenance plans for backups and so on. As we cannot deploy packages...

SQL Server 2008 : T-SQL (SS2K8)

Single Update Query - Required - Table 1: [code="sql"] Sno SID Sname 1 Null A 2 Null B 3 Null C 4 Null D 5 Null E [/code] Table 2: [code="sql"] ano aID aName 1 55 AA 2 32 BB 3 53 CC 4 10 DD 5 10 EE [/code] Requirement: I need to update Table 1 - Column SID values with Table 2 - Column aID in a single...

query returns empty row - hi all, please find the following code [code="sql"] DECLARE @intid as varchar(100) ='''BSEC0002''' + ',' + '''BSEC0001'''; PRINT @intid; DECLARE @qry as varchar(500) ='SELECT * FROM mstinstrument where...

Creating Stored Procedure - Hi, I have below table: ID URL 1 https://google.com 2 https://facebook.com 3 https://yahoo.com 4 https://gmail.com I am trying to create a procedure where I take the input...

DateDiff function help - Hi Friends, I'm new to TSQL and in my code I'm using - datediff(day,[WorkForce_JobPosting].[Job Posting End Date_JP] ,[WorkForce_WorkOrder].[Work Order End Date_WO]) >...

Data Insertion into Access DB using T-SQL - Hi Guys, I am trying to insert data from SQLServer into an access database,using T-SQL I used OPENROWSET to Select the data...

Combine variable and select statement - I want to combine this select statement and a variable(@EndUserDel) to get result.how can i do that? [code="sql"] declare @EndUserDel nvarchar(20) select...

FOR XML and trying to avoid nested cursors - Alright, history first. I've got a bit of a mess I've inherited. Groups inheriting groups inheriting... you get the drift....

Complex SQL QUERY with DateDIFF - Hi all, I need to script a query to discard access logs that not exceeding a predetermined margin (eg. 2 seconds)....

SQL Server 2008 : SQL Server Newbies

SQL Cached Plans - MPA - Hi All I have a question regarding SQL cached plans and the multipage allocator Based on what I understand, any request of...

Hash value generation times - I have a table with 56 million rows. I performed the following operation: ALTER TABLE MyBigTable ADD NewHashColumn AS HASHBYTES('SHA2_512', Column1+Column2+Column3) The...

Filtering by StatusId - I have a Stored Procedure which returns data based on the StatusId. DECLARE @StatusId INT=2 SELECT * FROM Employees WHERE StatusId=ISNULL(@StatusId,StatusId) If the StatusId is...

Best way to store images - Hi. I recognized if we store an image in binary type in SQL SERVER they take more space. For example a...

SQL Server 2008 : SQL Server 2008 Administration

Replication from SQL 2008R2 to SQL 2012 - Gurus, I have weird replication issue. Can't figure out what the issue is here. Please help me with ideas/known bugs(if...

DR/HA proposal - I need to write DR/HA proposal any suggestions?

Big table - We have a lookup table GeographicLookup table with a primary key called gisGeorgraphicLookup. It now has 2 million records, with each...

Monitoring error - I'm using Red-Gate Monitor tool to help me in managing our SQL Server. I see very frequently these two messages:...

Daylight saving time and SQL agent job - I have a SQL agent job scheduled at 2:00 am on Sunday. But it was triggered at 2:59:50 am instead of...

Another "cannot generate SSPI context" error - Freaking odd one here guys. :unsure: 2 SQL 2008 R2 servers on the same domain, but in different data centers. Live...

Missing index statistics - Dementia ? - I use a script showing me the missing index statistics with impact and so on. I think you all know...

Proper splitting and placement of files ? - Hi Folks, what ist the proper placement of files? In an article I red : LDF and MDF on separate drive, TempDB on...

Negative Available Space for tempdb - I'm checking all my databases and when I look at my templog setting in the Shrink File window I see...

Trace flag 4199 - Generally do you run with this on or off? I've always been slightly worried about setting this trace flag to ON...

Audit triggers - Dear Experts How to use triggers in auditing and in which cases Can any one provide examples Thanks

Reporting Services Logs not being deleted - We have a server running SQL 2008 SP1, Standard. We recently discovered that SSRS isn't cleaning up it's log files....

Career : Employers and Employees

Client does not want to pay overtime. How to deal with it ? - I am hourly-paid employee of a consulting company, but work all the time at client site. Sometimes I have to...

SQLServerCentral.com : Anything that is NOT about SQL!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Counting for Labels - I am running all of my production labels using SSRS 2008R2 I am trying to constuct a label that will Print...

SSRS report to show the results of two datasets at different granularity on same report - I need to build a report to show the Top n products and sum of the rest products across column...

need help on Expression syntax - Hi all, I am trying to create the Expression equivalent of the following query: SELECT count(TasksName) FROM [db].[dbo].[testdetails] where TopLevelProjectName = '40 Project Folder' and...

Thread being aborted at subscription - Hi , I receive very often the error message 'Thread being aborted' at several subscriptions of different reports. The strange thing is...

Reporting Services : Reporting Services 2005 Development

Is it possible to alter the .XML in and RDL file via SQL script? - Hi Guys, We have a 3rd party developing our data warehouse, and we do the SSRS devevelopment in house via...

Data Warehousing : Integration Services

Merge,Look Up,SCD which one is good for millions of records - Hi, I am new to SSIS. I have to load 50 millions of records(full load) into a SQL Server table. After the full...

Update Data in Prod table. Lookup ? Merge? - Hi, I am in need of a solution. I am supposed to load the data of a table from PROD server...

Problem to create a CONNECTION MANAGER inside a SSIS - Hi to all. I currently use SSIS of SQL SERVER 2008 R2 to create data flow to import data from several...

Excel import - force column to be string (SSIS 2005) - I know this topic has been covered in a few places - but it reoccurs regularly and I have NOT got...

Data Warehousing : Analysis Services

Restricting admin permissions - Hi I have a strange request from our BI department and I'm pretty sure it's not possible, but just want to...

Different aggreation results with and without a dummy WITH clause - I'm trying to diagnose a bug in a calculated measure in a SSAS cube, and while debugging I get this...

Login failed for user 'NT Service\MSSQLServerOLAPService' 28000 - I am using SQL Server 2012 developer edition on Windows 7. I have the following error attempting to process a...