Jeff Moden

Forum Replies Created

Viewing 15 posts - 6,256 through 6,270 (of 59,101 total)

Reply To: Execution Plan

Grant Fritchey wrote:
If you're rebuilding the indexes, then don't update the statistics. An index rebuild updates the statistics with a full scan. You don't need to run that after a rebuild.
Just...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 15, 2020 at 12:56 pm

#3797578
Reply To: Clustered Indexes on GUIDS, Complex chains of triggers and Heaps

It's an audit table, Scott. Most people are going to be doing a lot of reads from it. You're talking about doubling the size but a lot of people also...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 15, 2020 at 7:12 am

#3797505
Reply To: Execution Plan

I would run the stats rebuilds long before I ran the current industry standard for supposed "Best Practices" (Reorg between 5 and 30% logical fragmentation, REBUILD for more than 30%)......
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 14, 2020 at 9:47 pm

#3797378
Reply To: Clustered Indexes on GUIDS, Complex chains of triggers and Heaps

Just went through all that code and lordy.
First, they're using a MAX datatype on address columns. I guess they never want to do lookups on those columns. 😀 Either that...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 14, 2020 at 9:17 pm

#3797366
Reply To: SQL Server Fragmentation- Defragment

frederico_fonseca wrote:
its 10k partitions according to the OP
Lordy... I wonder if the partitions are "hourly".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 14, 2020 at 5:21 pm

#3797248
Reply To: Varchar to datetime

Heh for a lot of folks, they actually did wait until it broke. 😀 Most every one else (that I knew at the time) waited until 1999. I made my...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 14, 2020 at 5:19 pm

#3797246
Reply To: Varchar to datetime

ScottPletcher wrote:
Jeff Moden wrote:
It also increases the number of characters to be transmitted by a whopping 25% compared with "YYYYMMDD" and it was specified and designed in an age where 110-300...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 14, 2020 at 4:04 pm

#3797222
Reply To: SQL Server Fragmentation- Defragment

Lordy... looking at this again, I see partition numbers that exceed 5500 partitions. If you divide 5500 by 365, that's 15 YEARs of days if you've partitioned by day. I'm...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 14, 2020 at 3:41 pm

#3797201
Reply To: Why when I use variables on SQL the Database Engine avoid the indexes?

Spot on, Grant. Another thing that the use of variables affects is actually whether or not even properly setup "Minimal Logging" will occur. If you're trying to do Minimally Logged...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 14, 2020 at 3:17 pm

#3797187
Reply To: Varchar to datetime

jcelko212 32090 wrote:
To keep the record straight, the ANSI/ISO SQL standards allow only the ISO – 8601 "YYYY-MM-DD" format for dates. The. The reasons we pick this one were:
1) it's not...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 13, 2020 at 11:28 pm

#3796932
Reply To: Varchar to datetime

<headdesk> 😀 Fixed the post.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 13, 2020 at 3:56 pm

#3796760
Reply To: Sudden PLE drop

Sergiy wrote:
Look for queries which are heavy on BLOB processing.
You mentioned BizTalk - do you have some massive FOR XML queries? It may be either massive XML (JSON, you name...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 13, 2020 at 4:18 am

#3796570
Reply To: Varchar to datetime

Ah... almost forgot. One of the things that MS did very, VERY wrong is how they interpret the (secondary ANSI) format of YYYY-MM-DD when used for direct conversions from strings...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 13, 2020 at 3:14 am

#3796554
Reply To: Varchar to datetime
Phil Parkin wrote:
Ugly, but this works:
```
DECLARE @SchedStart VARCHAR(25) = '2020-06-07-12.30.00.00000';

SELECT @SchedStart
      ,CAST(STUFF(STUFF(STUFF(LEFT(@SchedStart, 23), 11, 1, ' '), 14, 1, ':'), 17, 1, ':')...
```
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
October 13, 2020 at 12:06 am

#3796525
Reply To: Varchar to datetime

Just a different take on the same subject...
If the dates and times always follow the same format, which would also be required when using STUFF, then the hidden ANSI capabilities...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 13, 2020 at 12:00 am

#3796521

Viewing 15 posts - 6,256 through 6,270 (of 59,101 total)