Jeff Moden

Forum Replies Created

Viewing 15 posts - 19,726 through 19,740 (of 59,098 total)

RE: Percentage of Fragmentation increased after 6 hours

coolchaitu (8/30/2015)
Dear Jeff sir,
I did not rebuild ALL indexes in the database with fillfactor=90. Only on some tables, index fragmentation was greater than 95%. For these indexes, i rebuilt with...
--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)

August 30, 2015 at 10:10 am

#1823265
RE: Help Needed in Performance Tuning

Thanks for the answers. It's kinda late tonight but I'll build a shedload of data tomorrow based on your answers and we'll dive into indexes and use of clustered...
--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)

August 29, 2015 at 9:27 pm

#1823245
RE: Help Needed in Performance Tuning

KGJ-Dev (8/29/2015)
Hi Scott thanks for the reply.
Hi Jeff , thanks for the reply and yes, the dump table has 3 million same kind of data. I also mentioned in...
--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)

August 29, 2015 at 4:07 pm

#1823235
RE: Help Needed in Performance Tuning

ScottPletcher (8/29/2015)
I, too, await what Jeff will propose as the best method to identity the proper clustered indexes, since we clearly very differently weigh what's most important in that regard....
--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)

August 29, 2015 at 3:51 pm

#1823234
RE: Use a select within a table field in a 2nd query

I'll also add that, for the example given, I see no need for the script table at all. The only thing that's changing is a value in the WHERE...
--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)

August 29, 2015 at 3:46 pm

#1823232
RE: Calling a stored procedure from inside another SQL select statement

You could use INSERT/EXECUTE (requires the precreation of the table to store the results of the stored procedure in) or you could use sanitized inputs to create a dynamic call...
--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)

August 29, 2015 at 3:41 pm

#1823231
RE: Are stored procedures the most secure way to access a database?

andrewbb (8/29/2015)
Is it possible hackers deliberately marketed non-SP solutions to make hacking easier?
If the code built in the managed code is properly parameterized instead of consisting of concatenated dynamic SQL,...
--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)

August 29, 2015 at 3:37 pm

#1823229
RE: Percentage of Fragmentation increased after 6 hours

coolchaitu (8/29/2015)
Good morning Gail Madam and everyone,
I reduced the fill factor value, set it to 90 and then rebuilded the indexes. It worked for majority of indexes. However, for some...
--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)

August 29, 2015 at 3:34 pm

#1823228
RE: Help Needed in Performance Tuning

In the process of considering a clustered index, you can't just consider SELECTs. You also have to consider page splits during inserts and updates and many other factors like...
--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)

August 29, 2015 at 3:00 pm

#1823225
RE: Help Needed in Performance Tuning

Except for two foreign key errors (you can't put FK's on Temp Tables), your attached code ran up to the final SELECT to the display as you posted it in...
--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)

August 29, 2015 at 1:36 pm

#1823220
RE: Object Relational Modeling (ORM) Examples

diegosiao (8/28/2015)
Sorry Jeff, but you do not have the authority to make this statement,
People who don't really know much about databases are the ones that frequently suggest the approach...
--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)

August 29, 2015 at 12:58 pm

#1823219
RE: SQL Performance Elevenses

They're all excellent but I like #11 the best, as well. People that believe that "Code First" efforts work well really need to study up on Aesop's fable 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)

August 28, 2015 at 7:03 pm

#1823190
RE: Not Exist causing major performance problem

Welsh Corgi (8/28/2015)
I have the following code that includes a Not exist and when I run it takes 4 minutes. If I exclude it take 40 seconds to run.
My thought...
--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)

August 28, 2015 at 6:55 pm

#1823189
RE: TempDB changes initial size after shrinking

palmeralex (8/28/2015)
Hi
I've had a bit of a strange experience when shrinking tempdb. Before everyone points out how stupid it is to shrink it, please read through the entire thread...
--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)

August 28, 2015 at 5:21 pm

#1823176
RE: T-SQL MDF & LDF size for spreadsheet

ScottPletcher (8/28/2015)
Jeff Moden (8/28/2015)
Why not just do a [font="Courier New"]SELECT SizeMB = Size/128.0, * FROM sys.Master_Files [/font]and call it a day?
I thought sys.master_files was not always kept current, particularly for...
--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)

August 28, 2015 at 4:56 pm

#1823171

Viewing 15 posts - 19,726 through 19,740 (of 59,098 total)