Tag Info

Hot answers tagged

5

The CPU time is tiny compared to the amount of actual time. The second time the query runs, it's fast - presumably once the data is in RAM (being one of the few things which benefits from the second run). Sounds to me like the problem isn't SQL, but the disk. Notice the PAGEIOLATCH waits increasing while your query runs. Have a look at what's happening on ...


4

Every time I had to do this, I used the RML Utilities (x86 and x64 package download links can be found on this page). Basically, you just have to set up a server-side trace using one of the templates included in the package. The captured trace can be analyzed using ReadTrace (included in RML Utilities) and it populates a database with query analysis ...


4

In some cases, soft delete is not meant to be permanent. You can just defer the delete to some background job (say, right before you reorganize / rebuild indexes) so that the originating transaction doesn't have to wait for the deletes to occur (especially if you have cascading deletes, triggers, etc). In other cases, a soft delete is not a performance ...


3

In order to avoid overloading my server, the requests are queued and handled one at a time. That's the problem right there. You are not avoiding but causing overloading this way. Single row INSERT / UPDATE is dramatically more expensive than doing the same en bloc. Each statement has to be planned and executed separately. Depending on missing details ...


3

If the table has an index, you may want to consider the cluster command instead of re-creating it or using vacuum full. This will: Have the same effect on dead tuples - it physically re-writes the whole table Retains any existing indexes Might improve performance more than just removing dead tuples, depending on whether you will benefit from the clustering ...


2

Assuming we are talking about 1:1 relationships among all tables. Overall storage is practically always (substantially) cheaper with a single table instead of multiple tables in 1:1 relationship. Each row has 28 bytes of overhead, plus typically a few more bytes for extra padding. And you need to store the PK column with every table. And have a separate ...


2

In addition to being slow, the statement's results will not be what you expect them to (which causes it to be slow): From the PostgreSQL documentation: from_list A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the ...


2

I don't think you'll be able to get good performance while using OFFSET. The database must search through 1,000,025 rows of output from the inner query; even if you have a good clustered index on TaskResults the system doesn't know for certain that it can skip ahead to date X. But you do! Assuming this is for some kind of GUI, make a note of the earliest ...


2

The scripts go over all of your tables, rebuild all the indexes and update their statistics. The reasons they CAN improve performance is that they reduce the index fragmentation and force SQL Server to generate new execution plans to queries, that may be more suitable. I'm not sure, though, why you need to run the first command which disables the indexes.. ...


2

If the databases are going to live on the same server, then from a straightforward performance perspective, using a single database divided by schema vs. a bunch of separate databases isn't going to be any different. There is no "cost" to calling database A vs. database B (unless they are both set to auto-close, a "feature" you should avoid like the plague). ...


2

Have you tried this? SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ] FROM ( SELECT Id, StatusDate, 'Results_201505' as tbl FROM Results_201505 WHERE A = 0, B = 1, C = 3 UNION ALL SELECT Id, StatusDate, 'Results_201504' as tbl FROM Results_201504 WHERE A ...


1

There is no definite answer. It really depends on the data and the change you have performed. For instance, if you created an index that helped in the 100,000 records table, in many cases it will help with the 10,000,000 records table. However, if, let's say, you had a cursor and you've rewritten it to a set based solution, it will be good enough for the ...


1

There are two ways of determining this. RECENT VERSIONS OF MYSQL One is to use the performance_schema (assuming 5.6 or higher). There are many tables that you can query to get at this information, particularly if you have innodb_file_per_table = 1 in your my.cnf. If you do USE performance_schema there are several tables that can help you to find this ...


1

As in all things SQL Server, the answer is "it depends". A good database developer can design an application to mitigate SQL Server hardware requirements with attention to detail on database design, index/query tuning, and application design (e.g. appropriate data caching). So it seems you may have covered that part if you see index seeks rather than scans ...


1

After thinking about this, I decided to answer instead of comment to provide more detailed information. Yes, an INSERT statement can use an OUTPUT clause. It can be specified before the VALUES clause. See the SQL Server Books Online (http://technet.microsoft.com/en-us/library/ms174335.aspx) for the authoritative T-SQL reference I suggest you avoid ...


1

IMHO and in the described use-case, you will never use more than one core. The reason is that your workload is IO bound, not CPU bound. As your 3 connections are creating a new Index, each of those needs to read the whole table from disk: this is what is taking time, not computing the Indexes.


1

Answering my own question, as future readers will benefit from it : Seems like we might be hitting Longer latency for SQL Server 2012 database when you use Service Broker, database mirroring, and Availability Groups. This is fixed in SQL server 2012 SP2 CU1. The KB 2976982 has a typo(AlawysOn). So if you are searching by AlwaysON, it wont show up. After ...


1

Well, this is a bit of voodoo/shotgun debugging, but I've got it functioning okay for the time being. I set the extended_keys option to on, and also created a covering index on the 8-million-row table in question. Now I'm getting an execution plan that's even better than the acceptable plan the old server was coming up with (which was only using Magento's ...


1

If the data needs to be clean and relational before being presented in your UI, I'd use SSIS to handle reading, cleaning, inserting your data into SQL Server, handling rejects, and archiving the input files. If your data does not need to be clean/relational before presenting in the UI, then a NoSQL solution might work for you. By the way, Excel is horrible ...


1

I use something like this to create dynamic pivots with a dynamic number of columns. Perhaps it will help you with your solution. It's a single statement to avoid looping or cursors. declare @strSQL as varchar(max) declare @columns as varchar(max) declare @columns1 as varchar(max) set @columns = (select stuff ((select ',cast(' + quotename(columnname) + ' ...


1

Adding an example to Michael's answer. The problem with the PIVOT is two fold. 1st, it wants to aggregate. You can get around this by defining your dataset to be distinct and using MAX or MIN functions. But your example above makes that impossible, due to a user being able to have multiple answer sets for a given question. You would end up with only one ...


1

You can put the table in KEEP Pool cache and this may have a positive impact on your queries. I have implemented it in my system for a 16 GB partition and we did get good performance gains. However putting a table in cache does not mean that its data is loaded into memory automatically. It only means that since we are using a separate buffer pool for these ...


1

It achieves the same effect as a VACUUM FULL except that it can be executed within a transaction block. Of course, any indices or constraints on the original table would be lost in the given example. I would need to know some additional information to help explain the performance increase: Are there any indices or constraints on the original table? Is ...


1

Unfortunately there is not much you can do about this, this behaviour is by design. The problem manifests itself when user sessions time out because the report is taking too much time. You can try to improve the reports, or configure the session timeout to be a bit longer than the longest running report See this link for an explanation about the why and ...



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