New answers tagged

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 ...


9

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, ...


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;


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 ...


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

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. ...


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 ...


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.


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 ...


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 ...


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 ...


0

I do not think it will be worthwhile to change the type. You may save a byte or two for the length field of each column value if using TEXT or MEDIUMTEXT, but that will be insignificant compared to the size of your data. VARCHAR and TEXT columns are handled the same way in InnoDB, so there is no reason to switch to VARCHAR either.


0

On your production box you likely have to compete with other queries that you do not have to compete for resources with on your development instance. Also, if your development instance is running on your localhost, which also generates the query, versus the introduction of a network link (Busy, slow, error prone, WAN distant) then you will have an issue of ...


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 ...


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 ...


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 ...


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).


0

There are other things that can come into play when dealing with a production vs a development environment such as fragmentation/load/locks/amount of data/etc. I would first start by comparing the plans in mySQL using EXPLAIN. This will give you more information as to what the problem is.


0

I suppose you have used Innodb storage engine. Its probably due to the log file size of database. MySQL MariaDb both store the data on log first and then pushed forward to its own files later while service stop event etc. With time log file size increases, and that affects DB load, selecting and all. If its new DB then log files are small, less ...


0

Whether or not performance is affected will be a function of data volume and machine capacity. Given the capacity of modern hardware, it's hard to imagine ticket sales volume that couldn't be handled by the design you describe. However, there are changes I would recommend for correctness, and might improve performance as a secondary benefit. Your get ...


0

Reading and writing a lot to a table can be hard to to keep performant. Indexes could indeed hurt insert performance and locking is also an important aspect to keep in mind. There is another way to fix those problems - don't write directly to the database. For example, write the messages to MSMQ and use a Windows Service to insert them (batched) to the ...


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 ...


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 ...


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 ...


0

Sadly, stage/sql/Sending data (something that you will see on SHOW PROCESSLIST as Sending data) does not tell us much about the query internals. The thread is reading and processing rows for a SELECT statement... Is like saying nothing, why is it different in both cases? Your effort is not in vain, you have proved what is NOT affecting the query- ...


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, ...


0

Look into INSERT IGNORE ... INSERT .. ON DUPLICATE KEY UPDATE .. -- Either adds new row or updates existing row Both... require a PRIMARY or UNIQUE key to know what to IGNORE or what is DUPLICATE. avoid doing a SELECT first to see if something is there. avoid DELETE.


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 ...


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 = ...


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 ...


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

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 ...


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 ...


0

I agree with @ypercubeᵀᴹ, the comma join in the exists clause can be removed: select hostname, criticality, source, message, record_date from eventlog.logs l1 where not exists ( SELECT 1 FROM eventlog.rules r where l1.message like r.content and r.type = 'DROP' ) and criticality in ('High', 'Medium') and record_date > sysdate() - ...


0

@sean-gallardy I'm fully aware of these metrics and that is what I was collecting. Nevertheless, the SQL cluster was choking. And took more than 1 second (times from the client application). After raising an issue with Microsoft (which did not help). From the storage array there was no latency at all, so it wasn't the issue. Finally, I decided to ...


0

Some day WP will realize that their design of 1 database per user has a scaling problem. 1 user versus 100 users is probably not much difference. 1K-10K begins to show pain, depending on how many are 'active'. 1000 databases means 1000 subdirectories in the filesystem. That is some burden in the OS. (10K would be worse.) 15K tables may or may not be an ...


0

A sliding time scale is very efficient by using PARTITIONs. Implement 14 hourly partitions using PARTITION BY RANGE(..). Use REORGANIZE PARTITION to create a new partition every hour. Use "transportable tablespaces" (assuming you are using 5.6, or preferably 5.7, and InnoDB) to move the 'old' partition away from the main table to the archive table. More ...


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 ...


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

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 ...


4

The answer is kind of "no" but more "your question does not have an answer because it's based upon incorrect assumptions". 1) Tables are not really stored in a particular order. 2) Even if they were, how would the DB know that the question had been answered until it had finished reading the entire table. Putting things "up front" would not help. 3) What ...



Top 50 recent answers are included