Hot answers tagged

10

As far as loops go for this type of thing, don't worry about it. Loops and cursors have a bad reputation because there are normally better set-based approaches that are often faster. For admin stuff, sometimes loops are the only way, and no set-based ways of doing this spring to mind, although you can parallelise tasks with tools like start in DOS, SSIS, ...


7

It is not possible to directly connect part of the query text (e.g. GROUP BY) with a specific operation in the final execution plan. You can write a query to find plans that: Contain a Hash Match Aggregate; and The query text contains a GROUP BY clause ...which is not quite the same thing, since this will find plans where the grouping logic was ...


6

No, the SPID (Server Process ID) is assigned the moment an application establishes a connection/session to the database and is retained until the connection/session ends.


5

If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower? It depends. No - if you run exactly the same queries as before (no access to the new data at all). SQL Server's partitioning implementation creates a separate rowset for each partition, so when you create a partitioned index, it creates a separate b-tree ...


4

You would just pull the execution plans for the most relevant queries from the plan cache. I would query sys.dm_exec_query_stats, and join to sys.dm_exec_query_plan and sys.dm_exec_sql_text to get the plan and query text, respectively. Within sys.dm_exec_query_stats you can pull queries with the highest IO, CPU, execution count, etc. - whatever is most ...


4

From the details you've provided it seems reasonable that the IX_VeryLarge non-clustered index would support both queries you've shown in your question. You have the Payload column typed as VARBINARY(MAX) - if you expect large objects to be stored in that column, I'd likely not INCLUDE it in the index since that will cause the index to be much much larger ...


3

Unless you've badly coded it, then no. It is probably less then 10 writes per second. Even very write heavy databases are over 90% reads and under 10% writes. Consider though that such things can be replaced by, say, an indexed view on this table that looks like the separate table. Or the DML can be done in a stored procedure so it's all in one place for ...


3

Where a filtered index on a computed column is too limited, you have the option of creating an indexed view. The indexed view is maintained automatically by the database, so you do not need to worry about getting trigger logic correct for all possible DML operations. You also do not have to worry about complicated correctness problems under high ...


3

Indexes The decision to add one or more nonclustered indexes is an assessment only you can make accurately, based on the results of testing, and taking into account your local priorities. That said, the performance impact of adding a small number of narrow nonclustered indexes is typically small, from the database's point of view. More generally, the ...


3

Turning my comment into an answer. There seems to be some misunderstandings of how certain performance counters work when it comes to availability groups. Only Synchronous Commit replicas will be counted toward Transaction Delay (ms)/Sec Asynchronous Commit replicas will not change this counter as they transfer no mirrored transactions (yes, this could have ...


3

Here's one method that doesn't require a looping construct: DECLARE @sql nvarchar(MAX) = N''; SELECT @sql += N'ALTER DATABASE ' + QUOTENAME(name) + N' SET RECOVERY FULL; ' FROM sys.databases WHERE database_id > 4 AND name NOT IN ( N'distribution', N'SSISDB' ); PRINT @sql; --EXEC(@sql); I agree with wBob's answer in that one need not be ...


3

...once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds. This seems to indicate that the storage subsystem is inadequate. If this is the cause, SQL Server will probably be waiting with one of the PAGEIOLATCH_XX wait types. It definitely appears that the LOB data must be loaded into memory on the ...


3

There are a number of different factors involved but it is entirely possible to have a replica that is synchronous be faster or slower than an asynchronous one. To list a few: Replica is under too much load Hardware specification differences (i.e. synch runs ssd and async runs rotational) or synch is using 8 cores @ 3.6 GHz and async is using 4 cores @ ...


3

If the reporter had any thoughts of rigor I would expect the hot cache / cold cache information to be included in the report. Otherwise it is like saying a car costs $y without saying which option, warranty, service or taxes that includes. Similarly I would expect to see it stated that subsequent tests were performed under the same conditions and how that ...


2

Your comment confirms that the original long script was using variables and these have become parameters. Probably you are benefitting from parameter sniffing here and better execution plans. The values for variables are not sniffed unless you use the ’option recompile' query hint so you will get the same plan regardless of what the runtime values are. ...


2

If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower? Wrong question. Yes, they will be slower - the index is deeper, more data must be accessed to filter. But the real question is: Will it be significantly or at least noticeable slower - and that is likely a no, because the index depth growth is NOT linear ...


2

What you're asking the DB to do in Query one is: Give me ALL from table A FILTERED Give me ALL from table B FILTERED Give me ALL from table C FILTERED Give me ALL from table D FILTERED And then Union. In the second query you first get all the data, and only after that you do the join and the filter. JOIN and WHERE on a UNION query, which doesn't really ...


2

No i disagree. I dont believe it should be faster on your laptop given the spec you have just described. I also dont believe a difference in version or flags should explain it either. A 9000% difference suggests something is going very very wrong. You should expect to get at least the same or similar result on the server, or even better. At this point ...


2

You have a major mistake in your code. MongoClient creates a connection pool. Even in large applications, it is hence usually a singleton. So you should have it as a global variable, initialize it in main and reuse it in each runnable. Which is perfectly fine, since MongoClient is thread safe. Another thing to keep in mind is that although the single ...


2

I thought about adding a computed column like this: ALTER TABLE dbo.mytable ADD Diff AS InboundQuantity - OutboundQuantity PERSISTED ; go CREATE NONCLUSTERED INDEX IX_WO_PlantCD_FilterInboundQtyNotEqual ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity) WHERE Diff <> 0 ; but that does not work. This is a bit of a kludge, ...


1

Im sorry so long not update this question. I solved this case 2 months after this question. After few times trial and errors, my biggest impact in performance is using separated harddisk between Operation System and Data. I try to bought new harddisk and look like changing storage system from RAID to AHCI give quite difference in performance for data ...


1

ObjectId is a misnomer if it is not unique. You are saying that it takes 4 columns to uniquely identify a row? Rethink. This WHERE does not make sense; it seems like you are over-specifying the row by filtering on so many things, including a flags: WHERE ObjectId = @objectId AND ObjectType = @objectType AND IsDeleted = 0 AND ObjectIdName = ...


1

See this example query for getting these execution plans. Now it's ordered by CPU consumption: SELECT qs.execution_count, qs.total_worker_time/1000 total_worker_time_msec, ((qs.total_worker_time/1000)/qs.execution_count) AVGtotal_worker_time_msec, qs.max_worker_time/1000 max_worker_time_msec, qs.total_elapsed_time/1000 ...


1

(The comments below apply to MySQL; some may apply to other engines.) UUIDs slow things down because of their random nature. Don't use Unicode; use utf8. (Better yet, CHARACTER SET utf8mb4) InnoDB keeps index fragmentation low by design. Rule of Thumb: In a table with millions of rows, a "point query" via the PRIMARY KEY can be expected to take about as ...


1

It's doing about 45k requests/sec Do not do 45k requests per second. On the insert side you can batch them and use batches of inserts. Possibly use a stored procedure - that should be a little less intense on the CPU (less parsing). But mostly consider whether you can cut down the number of operations. BulkCopy is quite nice and you can basically do ...


1

As others have suggested, I would try batching the incoming transactions. One approach could be storing incoming rows in an in-memory table (requires upgrading to Enterprise Edition) and then move batches of rows into their final storage using something like a SQL Server Agent job every five minutes or whenever you are sure they won't change again. ...


1

First, find out if "mail" guarantees that all data is utf8 or ascii or whatever. If utf8, then use MEDIUMTEXT CHARACTER SET utf8mb4 for a limit of 16MB. For a 4GB limit, change to LONGTEXT. If the mail is not forced into some character encoding, then use MEDIUMBLOB or LONGBLOB (without a charset).


1

Let's do the math. 6500 characters, even in CHARACTER SET utf8mb4 takes no more than 26000 bytes. TEXT has a limit of 64K bytes and needs a hidden 2-byte length field. LONGTEXT has a 4-byte length field. Let's say (for the 'math') that the average row length, including this text, is 3000 bytes. Math... Savings of switching from LONGTEXT to TEXT: 2 ...


1

Since you are running SP3 in QA and SP1 in production (assuming you are running with Trace Flag 4199) you are missing out on a lot of performance related fixes in your production environment. My first suggestion would be to test the query and compare the generated plan on servers of the same build number. Without details of the query and the underlying ...


1

The results from these views do not overlap and together cover 100% of the table. What keeps you from just querying the underlying table? Should be fastest: SELECT x.* FROM cases x JOIN case_clients cacl ON cacl.case_id = x.main_id WHERE cacl.client_id = 12046 ORDER BY x.sort_nr, x.id;



Only top voted, non community-wiki answers of a minimum length are eligible