Jeff Moden

Forum Replies Created

Viewing 15 posts - 8,596 through 8,610 (of 59,072 total)

Reply To: After 2014 to 2017 Upgrade, table unused space growing massively.

David Burrows wrote:
Jeff Moden wrote:
TF 1118 and 1117 are automatically implemented in 2016. You can turn them off for all databases except for TempDB. I wouldn't turn 1118 off anywhere but 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)

September 24, 2019 at 8:55 pm

#3683102
Reply To: What value need to be set for MAXDOP ?

I agree with the others about MAXDOP and Cost Threshold for Parallelism (CTP from here on) being more of an observational art rather than a science. I also agree 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)

September 24, 2019 at 3:44 pm

#3682975
Reply To: After 2014 to 2017 Upgrade, table unused space growing massively.

jimkern wrote:
I have a suspicion that the same behavior would have been seen in SQL2016, but no proof and no reason to personally research it at the moment. BTW...we are...
--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)

September 24, 2019 at 2:51 pm

#3682958
Reply To: Remove first comma from stuff

Papil wrote:
Hi,
I am doing STUFF in my query which is concatenating the blank data too and data looks like below. Sometimes there is space+comma and sometimes only comma in 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)

September 23, 2019 at 10:13 pm

#3682746
Reply To: After 2014 to 2017 Upgrade, table unused space growing massively.

Thanks for the detailed followup. Just to be sure, though... the problem/observations that I posted appear to be the same problem and it's with 2016, not 2017. Did I "get...
--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)

September 23, 2019 at 9:53 pm

#3682741
Reply To: After 2014 to 2017 Upgrade, table unused space growing massively.

jimkern wrote:
Not heaps. They do have clustered indexes as primary keys.
Most are BULK inserted to.
Rebuilding the indexes does help to recapture the space, but it only starts building again, which...
--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)

September 23, 2019 at 4:00 pm

#3682616
Reply To: Some T-SQL INSERTs DO Follow the Fill Factor! (SQL Oolie)

Thanks for the feedback, Paul. The articles of yours that I provided a link to are awesome. I'm still testing a bunch of things based on your excellent documentation. Thank...
--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)

September 23, 2019 at 3:08 pm

#3682583
Reply To: The log file of my user db is full. Please confirm if below resolves issue

No. Not quite right. I hope you've recently taken a full backup. If so, then you can take your log file backup and then immediately do a shrink if you...
--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)

September 22, 2019 at 3:34 am

#3682318
Reply To: slow database performance

Ok... first of all, you didn't say anywhere else that this was a "UAT" box. I thought it was a prod box because you were talking about licensing costs, etc. ...
--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)

September 21, 2019 at 9:23 pm

#3682303
Reply To: Selecting records and true or false value

This is a duplicate post and it's actually incomplete. Please, no replies here. See the following post instead. It's also been answered but don't let that stop you if you...
--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)

September 21, 2019 at 7:06 pm

#3682297
Reply To: Selecting records and true or false value

And now I see that you've posted while I was constructing a response. You can't actually insert into a TIMESTAMP datatype in SQL Server. With that in mind, which RDBMS...
--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)

September 21, 2019 at 7:03 pm

#3682296
Reply To: Selecting records and true or false value
Here's one simple method provided those are actual NULLs and not just a word of '[NULL]':
```
 SELECT  ProcessID
        ,IsCompleted...
```
--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)
September 21, 2019 at 6:56 pm

#3682292
Reply To: slow database performance
Oh... almost forget... your question was...
vsamantha35 wrote:
My question is, if all the queries are poorly performing. In that case, where do I start my troubleshooting.
The answer is, the system knows. ...
- This reply was modified 6 years, 9 months ago by Jeff Moden.
--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)
September 21, 2019 at 5:19 pm

#3682284
Reply To: slow database performance

I'd worry about memory but not in the way you distributed it. You have a fair number of fairly large databases. Adding memory is generally the single best hardware improvement...
--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)

September 21, 2019 at 5:06 pm

#3682281
Reply To: Query optimization help

My first observation is that there is no index to support this query even if the "table" was in good shape. You could try the following indexes to see 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)

September 21, 2019 at 4:05 pm

#3682273

Viewing 15 posts - 8,596 through 8,610 (of 59,072 total)