Tag Info

New answers tagged

0

WITH (NOLOCK) is the equivalent of using READ UNCOMMITTED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In any application with high transaction ...


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

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

A simple delete operation of huge records does not physically deletes records unless you use TABLOCK option in heap deletion which deleted records and dealloctaed pages so that space can be used. In normal case pages are marked as deallocated in PFS( Page free space) pages and futher a background task called as ghost cleanup physically removes the record( ...


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


0

You do not provide much information about your system or program, but here are very general pieces of advise to improve the writing performance of your system: Number one reason why MySQL can be slow is because it has inappropriate queries: are tables indexed correctly? Do you have a fast and flexible table design? Are you using memory efficiently? ...


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


0

From looking at your query, I think the planner is making the right choice, you have no WHERE clause or LIMIT, so the database has to return every row anyway, so it has the choice look at the entire table and look at the indexes or look at the entire table. Have you tried restricting the rows with a WHERE clause or used a limit? I cant think of many time ...


0

Looks like the disk subsystem on Server B is performing worse than on Server A but, I used to see disk issues not entirely related to disk specs. You could collect some other performance counters such as physical disk --> avg disk sec write (> 25 ms very slow), memory --> page file usage (> 70% bad), cpu --> processor queue length (> 12 very bad), memory --> ...


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


0

Try the hint INLINE with abc as ( select /*+ INLINE */ count(distinct id) as my_count, some_column from large_view v where some_column in (...constant values here...) group by some_column ) select my_count from abc;


0

OK, i think i know the answer. The OPLOG collection is a capped collection. It overwrites over time. The profile level was set to 2 for a short period of time logging all operations. I guess it will take time to overwrite these operations on a capped collection again and as a result increase the op log window. Would be interested in anyone elses take on ...


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


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


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


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


0

The following is a long shot, as we do not know anything about your hardware, InnoDB configuration, and query specifics, but I bet you are using the wrong tool for the job (InnoDB Engine). What you are trying to achieve is creating a very heavy index (up to 127 characters, which may take -this is a broad approximation- 127*3 bytes per entry), which is ...


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


0

SUGGESTION #1 If catgeory_id, family, text are the only three columns in the table, use REPLACE replace into foo (catgeory_id, family, text) values ('1', 'test', 'my text'), (50k more rows); REPLACE is nothing more that a mechanical DELETE and INSERT. SUGGESTION #2 If you are deleting 50k rows of category_id 1, then do a DELETE of all of them first ...


0

pagefile.sys % Usage shows total system committed not what currently is utilized. This value can increase due to load when system finds out it has to back process with more page file. Have a look at below link for detailed explanation http://blogs.technet.com/b/perfguru/archive/2008/01/08/explanation-of-pagefile-usage-as-reported-in-the-task-manager.aspx I ...


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


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


0

No there are no specific bad things about using temp tables and temp procedures. For the temp tables you'll want to make sure that you've got indexes as needed when querying from them, but that applies to normal tables as well.


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

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


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


0

In my experience, a big table should not slow down the whole database. it can slow down the queries on same table though but that too will depend on the structure of table and indexes. You dont have to store the PDF in database, I'd recommend that you eliminate the BLOB column and make it a CHAR. then store the PDF files on disk and their names into ...


0

Yes, there are cases where the wait will be mostly 100% OLEDB and the hardware will seem idle. In my case, I tried to run a DBCC CHECKTABLE on a 26 GB table with a spatial index. It would run and run and run.... I moved it to my workstation (6 core Zeon, 16 GB, with 2 SDDs) with hopes of getting it to finish. It runs faster, but runs and runs.... I ...


0

If all you want to do is waste a lot of space, you don't need a fancy table at all. A single varchar(4000) (filled up) should be enough: SQL> create table foo(a varchar(4000)); Table created. SQL> insert into foo select rpad(to_char(rownum), 4000, '*') from dual connect by level <= 1024 ; 1024 rows created. SQL> exec ...


0

This is often one of the most mythical subjects of MySQL. To separate fact from fiction, you need to remember the strengths and weaknesses of each Storage Engine. Each Storage Engine and application will govern the workload, read I/O, write I/0, and tuning options. I have written many posts about how and when to pick one storage engine over the other: Sep ...


0

Changing the datadir requires the following steps: 1) Stopping the service completely mysqladmin shutdown / service mysql stop. 2) Changing the options on the config file for everything that points to the old location to the new one 3) moving the actual files and directories at the old datadir manually to the new location with cp/rsync (make sure that it ...


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


1

The problem you are actually facing is that you are calling two items asynchronously on your end, but they in fact need to run in order on the database end per your requirements. There is nothing that says the first call should finish before the second since it's asynchronous. The process you have in place does not make sure that happens. IMHO either use a ...


1

A Foreign Key constraint is primarily there to preserve integrity, though there are some performance benefits. The good thing about foreign key constraints is that they are always in force in the database, instead of depending on the ORM to enforce it. The software platform for your application can change, gaining or losing features, but if the database ...


2

Foreign keys can indeed speed up queries if they are trusted. If the engine knows that every value in ParentID in the Child table refers to a valid PK in Parents then it won't even look at that table to check. However, relational constraints are a good idea in general regardless. One philosophy behind constraints in the database is to use them as a ...


2

In Postgres, a unique constraint is implemented with a unique B-tree index. Per documentation: Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint. Indexes use the same basic storing mechanisms as tables: arrays of data pages. Indexes have some minor additional features. ...


0

Try ordering the table by start_IP ranges then put a clustered index into the table. The idea is you want to have your query only search the relevant uster for the match instead of doing a table scan. Since I assume the IP start < IP end you can use this knowledge to omit those ranges that fall above below the IP your searching for.


1

On the scale of a kilometer the difference between a geoid & a flat plane ought to be negligible. (ok, unless you are very close to the poles I suppose) Couldn't you just use a planer geometry distance formula? i.e. x^2 + y^2 < a^2 That might be much cheaper than so many trig calls? Another trick that comes to mind is using some sort of hash. e.g. ...


3

To expand on @RobFarley's answer, by storing spatial data as Lat, Long AND GEOGRAPHY datatype, you can perform much faster distance calculations, without any of the nested cos/sin/radians functions. There is a good example and tutorial on how to using the Spatial functions in SQL on MSSQLTips. My code below is looking for all locations saved in my database ...


2

If you are looking for fast query performance, and you "don't require a large amount of accuracy" then perhaps don't try looking for things within a circle centered on your location, but look for them in a square centered on your location. You didn't explain your use-case, but perhaps this might give a tolerable result. The calculation required is a simple ...


5

Create a computed column which converts your lat and long columns into a geography type using the Point constructor. Then put a spatial index on this computed column. Then your query can create a geography point from your circle centre, and compare distances. Should be very quick.


4

Scalar valued functions takes time to invoke and by the looks of it you are doing quite a few calls to CIRCLEDISTANCE. You could rewrite your function to a inline table valued function instead. create function CIRCLEDISTANCE ( @LAT1 varchar(250), @LAT2 varchar(250), @LNG1 varchar(250), @LNG2 varchar(250) ) returns table as return ( select ...


2

First, is it true that the ideal implementation for bulk storage is to use a flat file database? If you're talking about SQLite, it isn't a "flat file database". Sure, it's a single file database, but it's highly structured. Second, is it true that for concurrent reads & writes, PostgreSQL vastly outperforms a flat file database? PostgreSQL ...


0

This is trickier than it looks. I/O per second is a difficult thing to pin down. Just because the file system sees so many I/Os per second, doesn't mean that MySQL sees the same thing - there is such a thing as I/O buffering. I would look into MySQL 5.6 - in particular the performance schema (P_S)(Marc Alff wrote the P_S (*)). One SQL SELECT could read from ...



Top 50 recent answers are included