Tag Info

Hot answers tagged

24

The phrase ORDER BY 1 refers to different columns; in the first it will be id, in the second val. Since id is the key it will be indexed and the order by will be a trivial amount of work. To order by val, however, the system will have to retrieve every row, sort the complete table by val, then choose just one of those rows. Change both queries to order by ...


9

much more data size-wise is deleted Deleting a 100kb image blob is actually not a size-of-data operation. The blob is deallocated, not deleted, and there is no full-image logging. You can easily test this: create database blob go use blob go create table t (id int not null identity(1,1), blob image) go insert into t (blob) values ( replicate( ...


5

I wrote a series on SQLServerCentral about baselines that might be of interest to you: http://www.sqlservercentral.com/Authors/Articles/Erin_Stellato/351331/ And as Shawn so kindly mentioned, I also have a Pluralsight course. If you have more questions, feel free to contact me (erin at sqlskills dot com). Erin


4

The performance difference in your query is well explained by MG. I am going to address this: I've always believed that * queries should be avoided specifically for performance reasons. select * carries no particular penalties by itself, it is problematic when misused. In a single-table query it works just fine. now join that table to another with 20 ...


4

The book is assuming that PersonFriend is indexed on PersonID, but not on FriendID. It also seems to assume that Person indexes PersonID and Person independently. If this is the case, the first query comes back as {INDEX UNIQUE SCAN Person on Person => 'Bob' get back PersonID} {INDEX RANGE SCAN PersonFriend on PersonID => PersonIDs for Alice and Zack ...


4

You can use the following query to determine how much RAM is being used by each database: USE master; SELECT d.name, CAST(COUNT(1) AS BIGINT) * 8192 / 1048576 AS MBinMemory FROM sys.dm_os_buffer_descriptors bd INNER JOIN sys.databases d ON bd.database_id = d.database_id GROUP BY d.name ORDER BY d.name; To see memory used by objects in a specific ...


3

I will take you question point wise 1.In my opinion its higly unlikey for an unused large table to cause issue with query running for diffrent database. SQL Server memory is dynamic in nature if suppose large portion of memory is occupied by datapages of DB1 Lazy writer and checkpoint pages will work together to age out pages which are not used recently or ...


2

Look at the original query select count(page_views.id) as views from playpack_media join page_views on page_views.itemId=playpack_media.media_Id where playpack_media.playpack_id = 1 and page_views.started_at BETWEEN '2014-06-23' and '2014-07-07' You should do a couple of things. First, create to compound indexes ALTER TABLE page_views ADD INDEX ...


2

The answer depends on what is really going on the wire (that is, in client session). First, what is a deadlock? It is a cycle in lock dependency graph. Whatever it means, to make a deadlock occur, you need to have some locks first. Locks normally do not live outside of transactions/queries. A connection itself cannot cause any deadlocks if it does not ...


2

I can only speak toward SQL Server. A database connection itself is not going to cause deadlocks. If it did connection pooling would be useless. How they are handling and managing transactions against the database can have an impact on deadlocks. There are plenty of scripts online that can be found on how to force a deadlock using SSMS, most are with two ...


2

Troubleshooting Performance It is all about the queries. You need only three bits of information about your queries: CPU, Duration & Reads. SELECT TOP 50 qs.creation_time , qs.execution_count , qs.total_worker_time as cpu , qs.total_elapsed_time as duration , qs.total_logical_reads as reads , t.[text] FROM sys.dm_exec_query_stats qs CROSS APPLY ...


2

What you should think about is not the just absolute values of wait_time_ms or waiting_tasks_count you should also look at the average wait times. When you look at an average wait, you should ask yourself: "Is this a reasonable amount of time to wait on this resource?" For example (if I am doing the math right) your PAGEIOLATCH wait is in the range of ...


2

Instead of using so many JOINs to get the result, you might get better performance pivoting the data using an aggregate function with a CASE expression. Oracle 10g doesn't have a PIVOT function so you'd have to use this type of query (aggregate/CASE) if you aren't going to use multiple JOINs on your table. Since you have a limited number of questions ...


2

The first thing to look at is db.serverStatus().ft. This has a bunch of metrics that may be helpful, to figure out where you're spending time. These are documented here: http://docs.tokutek.com/tokumx/tokumx-server-status.html Usually the way to improve query time is to make sure you have the right index for your query. You might be doing a query on ...


1

So in this case it does seem to have been the 8 tempDB files causing the biggest issue. I ran the analysis suggested here http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/ and found no PAGELATCH issues and a very high proportion of PAGEIOLATCH waits (I don't remember exactly, but ...


1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is at the transaction level or at the session level while NOLOCK is a query hint. You mentioned that you fully understand dirty reads, so using at the transaction level is what I would recommend. if you want dirty reads on some tables only, then NOLOCK hint will help you. SQL server 2005 and up allows you ...


1

DBCC CHECKDB isn't a good storage test. It does logical tests too, not just reads from disk - for example, it compares data between multiple indexes on the same table to make sure they all have the same values. These checks consume CPU cycles. If you want a better pure storage test, consider setting an artificially low buffer pool number and running ...


1

Please note the difference between the information_schema and performance_schema databases INFORMATION_SCHEMA The information_schema database is an inventory of all objects within the MySQL instance Such objects include: databases tables columns constraints indexes (called statistics) processlist locks I wrote a nice post about this 3 years ago : How ...


1

Single-column indexes may not be helpful enough and it requires a temporary table. In fact, they may be harmful as non-optimal indexes are being used with little information on why it is happening. The best possible indexes are (playpack_media.playpack_id, playpack_media.media_id) AND (page_views.itemId, pageviews.started_at) or ...


1

I doubt that the example you provide is a valid use case for temporary procedures (I don't see any benefit here to using #temp procedures over permanent procedures), but for #temp tables, which have a much wider set of use cases, the only way to fight these arguments with policy-setters is to run the code - using a full load and during typical workload ...


1

Does that mean that columns should be ordered from most space occupation to least? No, not necessarily. You can play "column tetris" to minimize padding and thereby save some space. The rule of thumb I gave and you quoted is one simple strategy for basic types that require alignment. As I mentioned in the quoted answer, you can test the actual storage ...


1

It looks like 'page swapping' is the problem here. When you deal with one table at a time, its indexes and parts of the table are loaded into the RAM. If you keep dealing with the same table, it will be fast as the table already exists in the ram. On the other hand, if you keep changing the table that you are dealing with, then the data in RAM has to be ...


1

I've noticed there's no CXPACKET wait in you result set. Have you disabled parallelism and if so why? If you look at your MAXDOP setting I'm guessing it will be set to 1. There are occasions when this is ok but they are rare. I would enable parallelism on this box and tune your workload. I might reverse the order here. Tune then enable parallelism. ...



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