Jeff Moden

Forum Replies Created

Viewing 15 posts - 5,836 through 5,850 (of 59,089 total)

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
Reply To: question on Index Rebuilds

I don't know of a way to predict index REBUILD times. I can only tell you that I have a similar table that has a similar row count and slightly...
--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 5:23 pm

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

With apologies for my ignorance on the subject, I'm not sure what's going on because I don't actually use index maintenance plans, mostly because of the many reported horrors about...
--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 5:03 pm

#3825213
Reply To: I need help with dates in the where clause I need to get data from 5pm to 5am

Just to add fuel to the fire, what do you want done if something overlaps the shift boundaries? Or is that simply not possible because you're truly only using a...
--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 4:57 pm

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

GBeezy wrote:
All great answers and all of them very helpful!
I will be using the used_page_count for my calculations.
Again, I appreciate everyone's help!
That still won't be accurate for the reasons I...
--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 4:52 pm

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

The problem with using "Reserved_Page_Count" is that it actually has little to do with row size. For example, if you have an index that has suffered a large number of...
--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 4:35 pm

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

My first question would be... how did you determine that all those indexes were rebuilt just because they had a frag level > 0?
Also, a screen shot of your "Rebuild...
--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 3:27 pm

#3825155
Reply To: Deleting folder on a drive

It would appear that you forgot to attach the code but, based on the narrative above, it sounds like you're on the right track to a fix.
--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 1:55 pm

#3825109
Reply To: Reaping the benefits of the Window functions in T-SQL

I'm one of those DBAs that will allow the use of CLR. I'm just not one to allow the use of CLR that doesn't make sense. I had one guy...
--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 28, 2020 at 1:05 pm

#3824687

Viewing 15 posts - 5,836 through 5,850 (of 59,089 total)