The description is vague. Is "new environment" a different server? Is it a different edition of SQL? Are the db files on SAN?

If, for example, the dbs...

As others have noted, never create indexes just because dta recommends them.

More importantly, though, the single most important performance factor is first getting the best clustered index. (Barring some...

SELECT TOP (1000) --remove after testing!

t.OPENDATE, t.ASSNDATE,

CAST(minutes_diff / 60 AS varchar(3)) + ':' +

...

Steve-0 (5/5/2014)

---

Hi Everyone thanks for the replies.

For the first 2, I'm not using static dates as per your queries.

Scott, your query is just casting values for actual field...

SELECT

OPENDATE, ASSNDATE,

CAST(minutes_diff / 60 AS varchar(3)) + ':' +

RIGHT('0' + CAST(minutes_diff % 60...

DECLARE @date_with_month datetime

SET @date_with_month = GETDATE()

;WITH

cteDays AS (

SELECT 1 AS day# UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL

SELECT...

I'd try to do this in the most straightforward way possible. Therefore, I'd go with db snapshots unless the modification activity on the dbs during the ETL process was...

Look at existing index usage and missing index stats. The first thing is to verify that you have the best clustered index. Once that is done, you can...

Edit: Based on the index missing and index usage stats you posted (thanks!) I would say: /Edit.

JID, not DID, seems like the best clustering index key for SELECTs. We...

Jeff Moden (4/29/2014)

---

ScottPletcher (4/29/2014)

---

For only 5 chars, I wouldn't bother will all the CTEs and related folderol.

Why not just:

SELECT

ISNULL(CAST(ASCII(SUBSTRING(data, 1, 1)) AS varchar(3)), '') +

...

I believe OP said that Enterprise Edition was not an option.

Eirikur Eiriksson (4/29/2014)

---

ScottPletcher (4/29/2014)

---

Then expand the initial code to handle 10 bytes (or 20 if you're that worried about it). Yes, I'd be willing to revisit code if the...

Then expand the initial code to handle 10 bytes (or 20 if you're that worried about it). Yes, I'd be willing to revisit code if the known 5 bytes...

For only 5 chars, I wouldn't bother will all the CTEs and related folderol.

Why not just:

SELECT

ISNULL(CAST(ASCII(SUBSTRING(data, 1, 1)) AS varchar(3)), '') +

...

What indexes does SQL report are missing? What is the usage of existing indexes on that table?

There's a reasonable chance that the table should be clustered by [DID] rather...