A community of more than 1,500,000 database professionals and growing

Stairway to Transaction Log Management in SQL Server, Level 7: Dealing with Excessive Log Growth

Gail Shaw, Tony Davis from SQLServerCentral.com

This level will examine the most common problems and forms of mismanagement that lead to excessive growth of the transaction log. More »

SQL Saturday #181 - Tokyo

Press Release from SQLServerCentral.com

A free day of training, SQL Saturday comes to Tokyo, Japan. Come have a SQL Server day with fellow data professionals if you can. More »

From the SQLServerCentral Blogs - Migrating SQL Server Databases that use Database Master Keys

matt.bowler from SQLServerCentral.com

There’s a lot of things to consider when migrating databases between SQL Server instances. I want to provide some tips... More »

In the latest versions of SQL Server, there are some amazing new features. Many of them allow us to expand the capabilities of SQL Server, but some are added to allow us to dive more deeply into how the system works. A couple of the newer DMVs are fantastic tools to allow us to find indexes that are unused, duplicate, or unneeded. If you're not using sys.dm_db_index_usage_stats or sys.dm_db_missing_index_details, you should dig into a little and learn how these work. However running a diagnostic query to find unused indexes and then dropping those indexes is a bad idea. You need to ensure that those indexes aren't rarely, or lightly used.

I thought about this recently while giving a talk on maintenance. Indexes require routine maintenance, and many of us schedule rebuilds or reorganizes in our databases to ensure that fragmentation doesn't become an issue. That's a good start, but there's more you can do.

Every month or two you should schedule time to analyze your indexes. Capture a workload from a Trace and analyze it with the Database Tuning Advisor. Take the results and compare them to your current indexing schema. Make a judgement or two on which indexes are used by different queries and spend a few hours testing changes to your systems. You might need new indexes, you might want to remove old indexes that aren't being used, or you might decide to add a column or include to an existing index.

There are lots of articles on SQLServerCentral and blogs on indexing that can help you learn more about what changes might improve performance, but ultimately you will really need to test any changes on your own systems. With a little practice, you can build a short routine that allows you to take a few hours every month and analyze a few indexing changes, perform a little testing, and perhaps greatly improve the performance of your applications.

Steve Jones

The Voice of the DBA Podcasts

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

• Watch the Windows Media Podcast - 18.5MB WMV
• Watch the iPod Video Podcast - 19.5MB MP4
• Listen to the MP3 Audio Podcast - 3.9MB MP3

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:

Steve Jones from SQLServerCentral.com

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

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

Today's Question:

CREATE TABLE T1 ( ID INT, Name VARCHAR(20)
)
CREATE TABLE T2 ( ID INT, Name VARCHAR(20)
)
INSERT T1
VALUES ( 1, 'A' ),
( 3, 'AA' ),
( 4, 'AAA' ),
( 6, 'AAAA' ),
( 7, 'AAAAA' )

INSERT T2
VALUES ( 1, 'B' ),
( 2, 'BB' ),
( 3, 'BBB' ),
( 6, 'BBBB' ),
( 8, 'BBBBB' )

Which of the following queries give the right answer (2,4,7,8) ? (Choose 2)

-- 1
SELECT a.id FROM T1 a LEFT JOIN T2 b ON a.Id = b.Id WHERE b.Id IS NULL 
UNION
SELECT b.id FROM T2 b LEFT JOIN T1 a ON b.Id=a.Id WHERE a.Id IS NULL

-- 2
SELECT b.id FROM T2 b LEFT JOIN T1 a ON a.Id = b.Id WHERE b.Id IS NULL
UNION
SELECT a.id FROM T1 a LEFT JOIN T2 b ON a.Id = b.Id WHERE a.Id IS NULL

-- 3
SELECT b.id FROM T1 a RIGHT JOIN T2 b ON a.Id = b.Id WHERE a.Id IS NULL 
UNION
SELECT a.id FROM T2 b RIGHT JOIN T1 a ON b.Id = a.Id WHERE b.Id IS NULL 

-- 4
SELECT a.id FROM T2 b RIGHT JOIN T1 a ON a.Id = b.Id WHERE a.Id IS NULL
UNION
SELECT b.id FROM T1 a RIGHT JOIN T2 b ON a.Id = b.Id WHERE b.Id IS NULL

-- 5
SELECT b.id FROM T2 b JOIN T1 a ON a.Id = b.Id WHERE a.Id IS NULL
UNION
SELECT a.id FROM T1 a JOIN T2 b ON a.Id = b.Id WHERE b.Id IS NULL

-- 6
SELECT b.id FROM T2 b JOIN T1 a ON a.Id = b.Id WHERE b.Id IS NULL
UNION
SELECT a.id FROM T1 a JOIN T2 b ON a.Id = b.Id WHERE a.Id IS NULL
 

By Gary Chinn

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.

Microsoft SQL Server 2012 Master Data Services

Harness your master data and grow revenue while reducing administrative costs. Thoroughly revised to cover the latest MDS features, Microsoft SQL Server 2012 Master Data Services, Second Edition shows how to implement and manage a centralized, customer-focused MDS framework. See how to accurately model business processes, load and cleanse data, enforce business rules, eliminate redundancies, and publish data to external systems. Security, SOA and Web services, and legacy data integration are also covered in this practical guide.

Get your copy from Amazon today.

DECLARE @string1 VARCHAR(16) = 'Microsoft Server'

DECLARE @Stuffing VARCHAR(30) = '******************************'

SELECT DATALENGTH(@string1),DATALENGTH(@Stuffing)

Execution of the above returns 16 and 30 as expected. I then execute : (Second SELECT statement)

SELECT STUFF(@string1, 11,0,@Stuffing)
     , DATALENGTH(STUFF(@string1, 11,0,@Stuffing))

The question is: what is returned as the DATALENGTH by the Second SELECT statement?

By bitbucket

Answer: 46

Explanation: I could not find an explanation of the "lengthing" of the declared item, the best I could find for the stuff function is: http://msdn.microsoft.com/en-us/library/ms188043.aspx/

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. An error is raised if the resulting value is larger than the maximum supported by the return type.

» Discuss this question and answer on the forums

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.

Intelligent Index Reorganize and Rebuild Script - v1.0

Tim Parker from SQLServerCentral.com

This script will provide a dynamic mechanism to decide between REBUILDING an index or simply REORGANIZING an index. Tested and approved for SQL Server 2005 & 2008 Standard or Enterprise or R2. More »

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

SQL Server 2005 : Business Intelligence

Importance of enhancing your fire safety knowledge - Residential fires, fires at workplace, and environmental fires can result in lethal burns and fatal injuries. Want to save your...

SQL Server 2005 : Data Corruption

corrupted mdf file - hello: i was given a corrupted mdf file, the server the database (sql 2000) crashed and the only file recovered...

SQL Server 2005 : SQL Server 2005 Integration Services

Data Import from CSV file - I have a CSV file which is having record like mentioned below. [code="other"] UserName User Id Type RamKumar ram MSAD RamaC rama MSAD RamesK rames MSAD Rameshv ramesh MSAD RamKumar ramp PCG Ramig rami ...

SQL Server 2008 : SQL Server 2008 - General

Database master key and symmetric keys protection - Hi guys, A quick question about encryption: What is the reason why the database master key can not protect symmetric keys directly...

Amount of data - I was wondering if there is a way to "trap" or track the amount of data (in bytes) that a...

Transactio log growth - hello experts, i have a scnerio in one of my production server, one table is very highly transactional (inserts and deletion...

Backup Overwrite - I have a requirement where i need to take daily backup with datetime stamp , the issue is we have limited...

how to write a stored procedure to check if the members membership has expired - i have a table named users where i have two three column userid, email, membershipexpiry i want to write a stored...

Keeping track of the number of times a record appears in query results - Goal: I have a web site for people to search for doctors. For marketing purposes for doctors, in my stored...

Dependent Delete statements - i need to delete the records from a table with multiple conditions , like below first i am clearing non numeric...

SQL IO Performance - I'm running out of brain cells trying to figure out what I've overlooked. Hoping someone has a quick checklist of...

SQL Server 2008 : T-SQL (SS2K8)

string with int - pretty basic so appreciate the help here,getting conversion error , what is the way to have int variable in the string? declare...

String manipulation with variable - i am trying to use @datestamp variable in simple string , but not luck , i dont want to use another variable...

Merge Statement over a linked server - Does anyone know a way of running a MERGE statement across 2 tables over a linked server? Getting the message "The...

read UNCOMMITTED run slower than read COMMITTED - Hello I have the following query which runs considerably slower when we change the isolation level to read uncomitted, I just...

Converting a string to a date - Hello everyone, I have 3 fields, all text strings but all supposed to represent dates. Field 1: varchar in the...

SQL Server 2008 : SQL Server Newbies

SQL find maximum of table generated with select - Hi guys. Newb in SQL with what i hope is an easy one: SELECT ACCUM.c1 FROM (SELECT t1.c1, SUM(t1.c2) as adding FROM Table1 t1 GROUP...

SQL Server 2008 : SQL Server 2008 Administration

permisssion - Hi, How do I make the users group i.e. domainname\users to be able to only select from tableA but user1 and...

SQLServerCentral.com : Anything that is NOT about SQL!

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...

Database Design : Design Ideas and Questions

Lots of Key Lookups vs. UniqueIdentifier Clustered Index - Hello. I'm working on a system that was originally designed with UUID (uniqueIdentifier) clustered indexes. Later the clustered indexes were rebuilt on...

Data Warehousing : Integration Services

Heeeelp !!!!!!!!!!!! SSIS Incremental loading. - Hi All, Excuse me for my bad english. I am newbie in SSIS. I need your help. Configuration: sql server 2008R2. I have ssis package...

SSIS process fails as a job but not when manually executed - Hi I have a SSIS process that is failing on a data pump step when it is executed as a job....

What is the best approach for defining server name in OLE DB connection for SSIS package running in different environments? - Hello, I am using SSIS in SQL Server 2008 R2 and have been creating packages for a few years, but have...