Viewing 15 posts - 2,551 through 2,565 (of 49,552 total)
inevercheckthis2002 (8/24/2016)
GilaMonster (8/23/2016)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 1:31 pm
Tac11 (8/24/2016)
or this is not even a issue?
Yup. It's normal, expected, desired behaviour. You really don't want SQL fetching data from disk every time it needs to read a row...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 12:38 pm
So put the GROUP BY inside the subquery, not replacing the DISTINCT in the outer query? It's not clear from the post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 12:22 pm
Luis Cazares (8/24/2016)
Also, using DISTINCT is expensive. In this case, you should try using GROUP BY.
Which will behave exactly the same as the DISTINCT. Since he wants distinct values, DISTINCT...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 12:00 pm
navtec (8/24/2016)
I do think maybe the max_lengths are large for those columns which may be prompting SQL Server to treat them as LOB objects.
The only time that a varchar/nvarchar column...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 10:33 am
Best thing to do is identify the queries which are slower under the new CE and tweak them and/or indexes to improve the performance.
The legacy cardinality estimator option is the...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 9:00 am
navtec (8/24/2016)
I have checked the index in question and the only column data types it has are
Clustered index, or nonclustered?
What's the complete table definition (post the CREATE TABLE) and index...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 8:56 am
navtec (8/24/2016)
Maybe there was a large amount of data in that nvarchar column which caused sql server to treat it as a LOB during online index rebuilding?
NVARCHAR(MAX) is a LOB....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 7:26 am
shirolkar.anand (8/24/2016)
you can use the following code to get the desired O/P-SELECT CityName,StateName FROM @Cities where CityName NOT IN (SELECT distinct CityName from @Cities WHERE StateNAme = 'ALL')
Which, other than...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 6:34 am
navtec (8/24/2016)
Still very strange why SQL Server would pick up on nvarchar data type as a LOB data type though.
NVARCHAR(MAX) is a LOB. Any other NVARCHAR is not LOB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 6:31 am
If there's no explicit transaction, then any changes that have been made and commited are persistent, any uncommitted are rolled back when the deadlock victim is killed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 5:23 am
prettsons (8/23/2016)
Microsoft offers DBCC CHECKDB with three options to fix database corruption issues: repair_rebuilt, repair_fast & repair_allow_data_loss. All these are able to fix database issues.
https://msdn.microsoft.com/en-us/library/ms176064.aspx
REPAIR_FAST
Maintains syntax for backward compatibility only....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 4:38 am
Depends whether you want the index creation to use TempDB for sorting or not.
That option isn't persisted anywhere, it's effective only for the create/alter that it's specified on and no...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 4:33 am
Rolled back to the beginning of the transaction. Hence if you have a procedure that does multiple data modifications without an explicit transaction, what had completed and committed is permanent...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 4:32 am
Filestream isn't a datatype. It's an attribute of a varbinary data type column. Bigint is definitely not considered as large object, it's a grand total of 16 8-bytes in size.
Your...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 4:29 am
Viewing 15 posts - 2,551 through 2,565 (of 49,552 total)