26,085 reputation
656103
bio website
location United Kingdom
age
visits member for 2 years, 6 months
seen Apr 3 at 19:03

Email: stackoverflowmartinsmith@gmail.com


Apr
3
comment Phantom insert query
That looks like an Entity Framework generated parameterised query possibly. (IIRC that uses the @@rowcount pattern shown) You could create an insert trigger on the table and log details such as application name, host name etc to help narrow the search down.
Mar
30
comment For SQL Server, What is the proper way to update a table?
Also look into recovery models, backups, and point in time recovery.
Mar
30
comment How should I limit a table to N rows, discarding oldest?
If I was to make it an answer I'd need to expand it. As (a) I'm on vacation (b) I'm on a mobile device. I'll pass on that!
Mar
30
comment How should I limit a table to N rows, discarding oldest?
You could also consider presizing the table with 1000 rows then just performing updates as a circular buffer
Mar
11
comment Comparing execution plans
@ypercube - Except if the query uses non deterministic expressions such as filtering on ROW_NUMBER ordered on a non unique column.
Mar
11
comment Comparing execution plans
You don't specify RDBMS and this question is far too broad anyway. SQL Server has various transformation rules that can be applied to sub trees to produce logically equivalent sub trees. It keeps track of these in the memo structure. See Query Optimizer Deep Dive and Inside the Optimizer: Constructing a Plan for some more details.
Mar
4
comment How to view an encrypted view or stored procedure
Connecting via the DAC is only the first step. It doesn't automatically de obfuscate it for you.
Mar
4
comment When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?
You could try updating statistics. A good recommendation is to take baseline execution plans of your procedures when all is running well to compare with when you encounter issues like this.
Mar
2
comment Where is the list of attached databases stored?
@JustinDearing - It is in master.sys.sysdbreg as this answer says. If you look at the execution plan for SELECT database_id,name FROM sys.databases you can also see this. To verify this in the DAC you can find out the physical location with SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM sys.sysdbreg then inspect those pages with DBCC PAGE for example.
Mar
1
comment GO After every T-SQL statement
Kind of. Even though the application only sends one group of statements to the server there's surely 4 batches there. A parent batch and three child batches. Though only one sqlserver.sql_batch_starting is fired.
Mar
1
comment GO After every T-SQL statement
+1 though the definition of "batch" used for the CREATE PROC rules is different from the first definition of a group of statements sent by the application. i.e. is this one batch or 3/4? EXEC ('CREATE PROC #P1 AS /*...*/');EXEC ('CREATE PROC #P2 AS /*...*/');EXEC ('CREATE PROC #P3 AS /*...*/');DROP PROC #P1, #P2, #P3;
Feb
27
comment Re-run a specific actual query plan
@Jeroen - Maybe undocumented TF 8780 that disables optimizer timeouts? Not sure if that would have the desired effect.
Feb
27
comment indexing on multiple nvarchar(max) columns
@agnes I agree moving these repeated long strings out of ProductDetail is a good move. I would also argue that chances are there is no need to hash these at all. Is a shipper name every really going to be longer than 450 characters? If not you can just use nvarchar(450) instead of max and stick a unique constraint on that. Even 450 is extremely generous. I can't imagine any sensible business would have a name even 100 chars long.
Feb
26
comment Re-run a specific actual query plan
@Jeroen - Hmmm. Not sure if there's any work around for that. Maybe there's some trace flag that would increase that limit but if there is I don't know it!
Feb
21
comment View scripts ran by a specific user?
That query shows you the text of queries that happen to be in the plan cache. It is absolutely not an exhaustive list of all SQL that was executed in the past 24 hours.
Feb
20
comment Converting char to varchar columns increases database size
14975.13 MB is not considerably greater than 17062.50 MB. The old fixed width columns stay in the row until you rebuild the table though
Feb
19
comment SPIDs stuck indefinitely in suspended state
You can add SET XACT_ABORT ON to the stored procedure to have the transaction rolled back on client timeout.
Feb
18
comment Method or Counter to track dropped connections in SQL Server?
I take it you already know about the connectivity ring buffer?
Feb
18
comment semaphore in SQL Server
What is the purpose of SET Free_SBS_NO = Free_SBS_NO?
Feb
17
comment Full Transaction Log on closed source system
Don't think that can be responsible. Try SELECT TOP (10000) * FROM sys.fn_dblog(NULL,NULL) and have a look at what is being logged. I just tried BEGIN TRAN SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; COMMIT and it doesn't write anything to the log file (no LOP_BEGIN_XACT / LOP_COMMIT_XACT pair)