Jeff Moden

Forum Replies Created

Viewing 15 posts - 5,806 through 5,820 (of 59,072 total)

Reply To: question on Index Rebuilds

Those are really good questions, Scott. First, I agree that the differentials are a great idea whether you're in the BULK LOGGED or SIMPLE Recovery Models. Better to be safe...
--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)

January 2, 2021 at 3:06 pm

#3826488
Reply To: question on Index Rebuilds

That's not true, Scott. The log can be applied. You just have to use the whole thing. You can't stop in the middle for a PIT restore. If something happens...
--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)

January 2, 2021 at 3:43 am

#3826355
Reply To: Help with Actual execution Plan

If you were to simply rename the actual .SQLPLAN as a .TXT file, you'd probably get more takers because most won't want to unzip a file. They don't mind renaming...
--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)

January 1, 2021 at 7:07 pm

#3826261
Reply To: Odd query question i did some basic digging but didn't find anything relevant.

Curious... what is the datatype for Col4?
--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)

January 1, 2021 at 7:05 pm

#3826260
Reply To: question on Index Rebuilds

ScottPletcher wrote:
Jeff Moden wrote:
ScottPletcher wrote:
Even if the db is not normally in SIMPLE mode, you would certainly want it to be in SIMPLE for these rebuilds, to drastically reduce logging requirements.
If...
--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)

January 1, 2021 at 6:36 pm

#3826258
Reply To: Rebuilding and Reorganizing Indexes

If you have fragmented indexes and you make the mistake of doing index maintenance on indexes with a "0" or "100" Fill Factor (they're both the same), you will suffer...
--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)

December 31, 2020 at 10:26 pm

#3826026
Reply To: While loop

I appreciate the unabridged version of the code but, like I asked above and on your previous post about this code, I see absolutely no reason at all for this...
--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)

December 31, 2020 at 7:25 pm

#3825977
Reply To: question on Index Rebuilds

Jeff Moden wrote:
ScottPletcher wrote:
With a table that large, say you have a total of 6 indexes, 1 clus and 5 nonclus.
I'd expect it to be faster to do a REBUILD ALL...
--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)

December 30, 2020 at 11:08 pm

#3825703
Reply To: question on Index Rebuilds

Bruin wrote:
I was thinking of using this script on my fragmented index rebuilds. USE [xxxx] GO ALTER INDEX [PK_EmployeeJobHistory_1] ON [dbo].[EmployeeJobHistory] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE...
--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)

December 30, 2020 at 10:55 pm

#3825688
Reply To: question on Index Rebuilds

ScottPletcher wrote:
Even if the db is not normally in SIMPLE mode, you would certainly want it to be in SIMPLE for these rebuilds, to drastically reduce logging requirements.
If the db...
--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)

December 30, 2020 at 10:52 pm

#3825687
Reply To: question on Index Rebuilds

ScottPletcher wrote:
With a table that large, say you have a total of 6 indexes, 1 clus and 5 nonclus.
I'd expect it to be faster to do a REBUILD ALL than...
--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)

December 30, 2020 at 2:44 am

#3825359
Reply To: Maintenance task for Rebuild index with fragmentation > 30%

Jeffrey Williams wrote:
It comes down to knowing what maintenance needs to be performed *for your system* and relying on a wizard...or some tool (Ola) to figure out maintenance for you 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)

December 29, 2020 at 11:04 pm

#3825327
Reply To: question on Index Rebuilds

Bruin wrote:
I'm going to do a REBUILD. If I have 3+ indexes along with PK, is there any rule on thumb to rebuild each individually or REBUILD ALL on the...
--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)

December 29, 2020 at 8:24 pm

#3825281
Reply To: question on Index Rebuilds

Sergiy wrote:
If you need rebuilding indexes it’s probably a good time to rethink the indexing strategy. you need to figure out what causes fragmentation and redefine the indexes in such...
--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)

December 29, 2020 at 8:10 pm

#3825278
Reply To: Find Average Row Size and Num of Rows per page for a table

GBeezy wrote:
Jeff,
Looking at https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-partition-stats-transact-sql?view=sql-server-ver15
It states that the used_page_count accounts or in row, LOB and overflow. And, looking at sp_spaceused, from what I can tell and from my simple example sys.dm_db_partition_stats.used_page_count...
--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)

December 29, 2020 at 8:09 pm

#3825277

Viewing 15 posts - 5,806 through 5,820 (of 59,072 total)