Stack Exchange
log in

Kin Shah

 Discussion on question by J.D.: How t…

Imported from a comment discussion on dba.stackexchange.com/qu...
Kin Shah
Sep 6, 2021 16:09
please change the power plan and try the tsql route.. also refer to my answer that I linked for additional steps like enabling trace flag to see where the time is spent.
Kin Shah
Sep 6, 2021 16:09
what is the power plan option set to high performance ? its not clear that you are using tsql or maint plan to take your backups - try using tsql with compression ? you can use the trace flags 3605 and 3213 to find out the default BUFFERCOUNT value used in your backup and then tune accordingly. see dba.stackexchange.com/a/82505/8783 that might help you further.
 

 The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....
10
1
Kin Shah
Jul 23, 2019 17:02
interesting behavior I observed - database A has snapshot isolation level enabled and database B does not.

A transaction starts in database A and have few joins to database B. We got error `Snapshot isolation transaction failed accessing database 'database B' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation`.

Interestingly, we got error for only few mins and then it went away. sys.dm_exec_requests shows that the transaction had read committed and it switched to snapshot on the db that does not have that isolation level enabled and …
Kin Shah
Jul 23, 2019 16:59
Hello All
Kin
Jun 21, 2018 11:29
@hot2use I am an alumni in Heap :-) .. use to hang out here but in my current job few things are blocked
Kin
Jun 21, 2018 11:27
To be honest - there is no need for using queues for what OP is trying to achieve. Just use event notification and be done with iit
Kin
Jun 21, 2018 11:17
seems like someone got pissed and started Downvotes to my answers :-)
Kin
Jun 21, 2018 11:16
Hello All
Kin
Jun 21, 2018 11:15
@hot2use I closed it as I thought it was dupe and it answers the 2nd part - Is there better method of altering on blocking than with queues and event notifications?
Kin
Jun 21, 2018 11:15
@hot
Kin
Oct 9, 2017 19:52
check table gives inconsistency error
Kin
Oct 9, 2017 19:52
@sp_BlitzErik No error
Kin
Oct 9, 2017 19:45
even dropping and recreating does not help ..
Kin
Oct 9, 2017 19:45
yeh .. same as Robert encountered sqlsoldier.net/wp/sqlserver/…
Kin
Oct 9, 2017 19:45
before I open case with MS
Kin
Oct 9, 2017 19:44
any hints ?
Kin
Oct 9, 2017 19:44
Tried all possible stuff - rebuild nonclustered index, rebuild partition - online and offline
Kin
Oct 9, 2017 19:44
Facing a weird issue .. Running select ID from table where ID = 123 (uses clustered index) returns no rows ...

Running same query with [non_clusteredIndex] returns 1 row
Kin
Oct 9, 2017 19:42
Hello ..
 

 Discussion on question by robdigm: Wh…

Imported from a comment discussion on dba.stackexchange.com/qu...
Kin
Jul 15, 2019 17:43
so the view runs slow when called from app vs when you run using SSMS ?
Kin
Jul 15, 2019 17:43
@robdigm - no. Just run select columns from YourView WITH (NOEXPAND);. Leave your view as is.
Kin
Jul 15, 2019 17:43
what did you ran ? Can you post it ?
Kin
Jul 15, 2019 17:43
Can you try using WITH (NOEXPAND); hint - select columns from YourView WITH (NOEXPAND); . Post the execution plan as scsimon mentioned.
 

 Discussion between Kin and Tomasz

Imported from a comment discussion on dba.stackexchange.com/qu...
Kin
Oct 8, 2018 19:21
cool
Kin
Oct 8, 2018 19:17
if sql server is using that or above memory then yes .. it will
Kin
Oct 8, 2018 19:16
if sql is not using that much memory .. then a restart wont be required
Kin
Oct 8, 2018 19:10
total RAM
Kin
Oct 8, 2018 19:10
since you have 500GB
Kin
Oct 8, 2018 19:10
start with 440GB
Kin
Oct 8, 2018 19:10
I normally keep max memory to 75% of total memory
Kin
Oct 8, 2018 18:59
ok .. is patching to latest possible ? might be hitting some bug
Kin
Oct 8, 2018 18:41
ok .. is the build on the latest ?
Kin
Oct 8, 2018 18:07
what is the max memory set to ? what is the db size ?
Kin
Oct 8, 2018 18:06
for mini dumps - just check select * from sys.dm_server_memory_dumps.
Kin
Oct 8, 2018 18:06
anything in windows event log - app or system ? any mini dumps generated ?
Kin
Oct 8, 2018 18:06
what is the total RAM on the server ? Is any sort of encryption e.g. forced encryption enabled ?
 

 Discussion on question by Element Zer…

Imported from a comment discussion on dba.stackexchange.com/qu...
Kin
Jul 13, 2018 13:25
Check windows event logs to see if there is any corruption or not.
Kin
Jul 13, 2018 13:25
1. Try backup .. with FORMAT, INIT or just use a new file. if 1st does not work, do a hex dump with style 2 PAGE ('MyDatabase',1,745345, 2);. Try restoring that page which has corruption. see this blogs.msdn.microsoft.com/sqlserverstorageengine/2007/01/17/…
 

 Discussion on question by Robert Gann…

Imported from a comment discussion on dba.stackexchange.com/qu...
Kin
Aug 29, 2017 11:18
Also, refer to terminate and reconnect method - BULK INSERT commands to alleviate SOS_SCHEDULER_YIELD (P.189)
Kin
Aug 29, 2017 11:18
@RobertGannon just curious, have you tried changing your MAXDOP away from default(0) and then rerun your query. In the execution plan, [epacube].[marginmgr].[SHEET_RESULTS].[IDX_SR_DNF_PSF_OESF_E‌​SF_SRID_INCL].(No‌​nCl‌​ustered) - the estimated rows are massive 4,271,040.
 

 Discussion on question by KASQLDBA: H…

Imported from a comment discussion on dba.stackexchange.com/qu...
Kin
Aug 29, 2017 11:13
Is max memory set correctly or it is also set to default ? I cannot think of alleviating the memory grants by the query ? May be freeing up proc cache and dropping the buffers will generate better plans .. This might have some initial impact on performance, but you can test and then try it.
Kin
Aug 29, 2017 11:13
No need to reboot.. changing MAXDOP is dynamic .. you just have to run RECONFIGURE with OVERRIDE. Are you still seeing high memory usage by the queries ?
Kin
Aug 29, 2017 11:13
Yes change it. Maxdop of 1 means no parallelism.
Kin
Aug 29, 2017 11:13
What is MAXDOP setting for your server ? Check this answer for how to calculate it.
 

 Discussion on question by user3224291…

Imported from a comment discussion on dba.stackexchange.com/qu...
Kin
Aug 29, 2017 11:06
@user3224291 Is CDC enabled ?
Kin
Aug 29, 2017 11:06
Is instant file initialization enabled ? Also, curious if the database is having lots of VLF's and this might bump up the restore times. Also, since this is a test server, to debug the entire restore process, is it possible to turn on DBCC TRACEON(3605,3004,-1). Note that 3004 is undocumented, but it will print out whats going on with the restore of database in the error log which will be useful.
 

 Discussion on question by hko: Sql Se…

Imported from a comment discussion on dba.stackexchange.com/qu...
Kin
Aug 29, 2017 10:59
so I need a textual representation of Sql server => then sql server is not right for you. Use Excel or notepad++. On a serious note, for migrations, usually a backup/restore is preferred - since you are scripting your entire database - why just not do backup restore ?
 

 Discussion on question by Hani El Mou…

Imported from a comment discussion on dba.stackexchange.com/qu...
Kin
Aug 29, 2017 10:30
@HaniElMouallem both Audit and main table + the code around line 818 so that we can help you out.
Kin
Aug 29, 2017 10:30
@HaniElMouallem Can you post create table statements as well ?
Kin
Aug 29, 2017 10:30
Then why dont you use CDC - change data capture ?