Tag Info

New answers tagged

3

The addition of the redundant predicate can make a difference in SQL Server. In the execution plans below notice the @1 in the first plan vs the literal 'foo' in the second plan. This indicates that SQL Server considered the first query for simple parameterisation to promote execution plan reuse - however the comparison of two constants prevents this ...


1

For SQL Server, using a similar example with a clustered index on the Id, the plans both use a "Clustered Index Seek" which is a broad explanation for what may happen internally. select ObjectId from oav.Object where ObjectId in (3105680,3105681,3105682,3105683,3105684,3105685,3105686,3105687,3105688,3105689) select ObjectId from oav.Object where ...


0

For what it's worth, to get the read-speed I needed I ended-up taking a different approach (although date I say it on a database Q&A site!). Instead of using a database table to store the data, I created a text file with one line per value, sorted alphabetically. Whenever I need to query to see whether a given value exists, I use a binary search ...


2

You should perform an UPDATE LEFT JOIN and set enabled based on the right side being NULL UPDATE table1 A LEFT JOIN table2 B ON A.code = B.id SET A.enabled = 1 - ISNULL(B.id); Why should this work ? If ISNULL(B.id) is 0, that means A.enabled is set to 1 (1 - 0) because it is in table2 If ISNULL(B.id) is 1, that means A.enabled is set to 0 (1 - 1) ...


1

we have run into one point of contention between developers and dba's: they will not run traces against production database servers. It depends on what you asking your DBAs to trace (I am only talking about server side trace). We have a 24x7 trace running which captures minimal stuff which helps us in troubleshooting specific scenarios e.g. User error ...


4

The latest version of the RML Utilities (09.04.0051 as at today) support Extended Events tracing, so maybe you could work with your DBAs to do some controlled tracing. A screenshot from the help file: They do however have a point because the default trace template provided with RML includes statement-level events (SQL:StmtStarting, SQL:StmtCompleted, ...


0

Zerothly, you need to test your backups and have a recovery plan if your not doing that. Lets talk about some quick win performance first of all. Here are some options. Google can help flesh these out more. Are their multiple named instances on the box? Have you set max memory? Have you set the number of files for tempdb? If its at all possible, put the ...


1

Has anyone done an research as to the overhead created by enabling AWE on the 64-bit OS/SQL combination? This is one of the Myths that Paul Randal clearly debunks in his famous series - A SQL Server DBA myth a day On 64-bit systems (2005+): AWE is not required (and in fact enabling it does nothing). Also refer to Fun with Locked Pages, AWE, Task ...


1

You can easily find the subsequence when you cast the arrays to strings and replace the curly brackets with commas: translate(cast(sequence as varchar(10000)), '{}',',,') {1,3,17,25,377,424,242,1234} -> ',1,3,17,25,377,424,242,1234,' Do the same for the array you're searching for and add a leading and trailing %: '%' || translate(cast(searchedarray ...


0

What you must do is increase the join_buffer_size. What is join_buffer_size ? The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full ...


3

Changing your table from a heap to having a clustered index should significantly improve your performance on both queries and perhaps even on inserts. Generally speaking, your clustered index should be narrow, unique, and ever increasing. Using a datetime that you can't guarantee to be unique is not ideal because it's 8 bytes and, since it isn't unique, sql ...


1

Consider using a table-valued parameter to pass many rows of data as a single proc call, or alternatively, bulk copy directly into the table. These techniques will improve insert throughput by orders of magnitude compared to singleton inserts, even if those are multi-threaded. An incremental clustered index will provide the best insert performance against ...


0

If this is going to be a Write Once, Read Many database, consider your NoSQL options like Riak. The key indexing the values can be anything you want and the record can be accessed quickly. If you're addressing a big data issue, where you want to bulk insert your records and then perform analysis on them, I suggest you go with a Hadoop cluster, upload your ...


24

This pattern column = @argument OR (@argument IS NULL AND column IS NULL) can be replaced with EXISTS (SELECT column INTERSECT SELECT @argument) This will let you match a NULL with a NULL and will allow the engine to use an index on column efficiently. For an excellent in-depth analysis of this technique, I refer you to Paul White's blog article: ...


1

Index Scan scans each and every record in the index. Table Scan is where the table is processed row by row from beginning to end. If the index is a clustered index then an index scan is really a table scan. Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Index Seek: ...


1

Your max_connect_errors is way too low. You have it at 1000. That means after 1000 consecutive connect failures, you cannot connect to MySQL any longer. Your status variable Aborted_connects should be the dead giveaway if it climbs fast. When you can no longer connect to MySQL even with bunch of open DB Connections, you would have to execute FLUSH HOSTS; ...


1

How about an indexed view? CREATE VIEW dbo.LoginsByDate WITH SCHEMABINDING AS SELECT UserID, LoginDate = CONVERT(DATE, LoginDate), LoginCount = COUNT_BIG(*) FROM dbo.LoginTable GROUP BY UserID, CONVERT(DATE, LoginDate); GO CREATE UNIQUE CLUSTERED INDEX IX_LoginsByDate ON dbo.LoginsByDate(UserID, LoginDate); GO What this does is ...


0

What maintenance should be performed on the table after a massive record deletion? When performing a large delete like this, SQL Server doesn't actually remove the data from the pages for you right away. It marks them as logically deleted and the ghost cleanup task will then remove them as it runs in the background. That said, here are two questions ...


3

You can rebuild or defrag, depending on the nature of the data that was deleted, the number of indexes, and how badly they were impacted. If you know the fragmentation before the delete, it would be easy to assess the delta from sys.dm_db_index_physical_stats; if you don't, then you could just apply the same rules you normally would when determining whether ...


1

For it worth : I"ve ran optimize table on the main tables of a database who works under heavy load. The main tables have been under great load of inserts/updates/deletes. The optimize reduced the table sizes by 90% and improved the system performance by more than twice !


12

The problem seems to be related to the fact that [TestGeocode].[ToString]() returns a max datatype (nvarchar(max)). I also encounter the issue with this simpler version (changing the definition of c1 to varchar(8000) or using COALESCE instead of ISNULL resolves it) DROP TABLE dbo.Test CREATE TABLE dbo.Test ( c1 VARCHAR( ...


1

I think it could be helpful for you to think of MapReduce as (essentially) a distributed query engine. I know it isn't one-to-one, but with SELECT and aggregate functions such as sum(), max(), etc being very much like a Map() operation, and GROUP BY being very much like a Reduce(), there is quite a bit of similarity. In your case, on the same hardware, I ...


3

The index that will give you the most benefit is one on fldB, fldC, fldA desc or on fldC, fldB, fldA desc depending on your data. You'll need to check your data and keys to determine which column should go first. The last column of the index is the column for which the maximum value is being computed. By including it in the index you will avoid table ...


1

As only fldC & fldB are in the filter criteria, an index with these two columns on the left side will be right. Try to index on (fldC, fldB, fldA), this should do the trick mostly.


0

You will get these symptoms due to a non-sargable expression if the data type of @HashKey doesn't match that of the indexed column. You might need an explicit CAST in the computed column expression to coerce the desired data type.


2

Looks like my short answer was not to everyone's taste, so let me show you the long answer First of all, let's create our lab. @WestFarmer mentioned a couple of tables SQL>create table A ( 2 some_column number, 3 another_column number 4 ); Table created. SQL>create table B ( 2 id number 3 ); Table created. Also a mentoned ...


2

It makes a lot of sense to me. The MySQL Query Optimizer looks over the WHERE, GROUP BY and ORDER BY clauses. Look at the first query select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1; Which index in sta_session has the most columns mentioned in the WHERE, GROUP BY, and ...


3

SELECT id FROM accesses WHERE token IS NOT NULL; The perfect index for this specific query would be a partial index: CREATE INDEX accesses_foo_idx ON accesses(id) WHERE token IS NOT NULL; The index condition is the important part. On top of it, since you only retrieve id which is covered by the index, you can get index-only scans out of this (if the ...


0

Expanding my comment on Ionic's answer a bit: If the instances have not had specific memory limits set then I suspect memory contention: the dev/stage/other instances have claimed a pile of memory so there is insufficient for the live instance(s) so queries that would otherwise be performed entirely in RAM are needed extra page reads from disk (because the ...


0

Pretty simple. Create the index and look at the query plan for an is null search. I tested in MS-SQL and the answer is yes search on is null used the index. Search on is not null also used the index.


1

You could always test this against your data and possibly use some sort of query analyzer. Since there are a lot of factors for "common considerations about indexes," you should consider things like: data size, data type, and inserts, updates, deletes and not just selects. How often do you run the query? Is this an occasional check for blanks? How often ...


1

Edit: not mentioned so far.... Check for auto-growth events: http://stackoverflow.com/questions/3752942/how-to-check-when-autogrowth-is-done-last Check the auto-growth settings. Make sure they are appropriate for the size of your database. If your files were shrunk you may be experiencing log or data file auto-growth in tiny increments causing queries to ...


3

Well here is a small checklist. Due to the fact that I'm not on your machine I cannot take a look inside and give you some suggestions. But I'm pretty sure, that you'll find the root cause with the provided statements. Wrong memory configuration As you mentioned, you upgraded your SQL Server and run different instances on the same physical machine. One of ...


0

Other alternative's are: SELECT DISTINCT l.a, l.b FROM links l LEFT JOIN t_i ON t_i.i_id IN (l.a, l.b) LEFT JOIN t_o ON t_o.o_id IN (l.a, l.b) WHERE COALESCE(t_i.i_id, t_o.o_id) IS NOT NULL; SELECT l.a, l.b FROM links l WHERE EXISTS ( SELECT 1 FROM t_i WHERE t_i.i_id IN (l.a, l.b) UNION ALL SELECT 1 FROM t_o WHERE ...


0

As I understand you want to check each combination of a/b and i_id/o_id then simple rewrite to join is like this: SELECT a, b FROM links JOIN t_i ON (a = i_id OR b = i_id) WHERE id = x UNION SELECT a, b FROM links JOIN t_o ON (a = o_id OR b = o_id) WHERE id = x But for good index usage I suggest splitting it once more. It is even longer ...


2

Memory related settings You've already addressed the key bottleneck for read heavy applications, that is, having sufficient RAM for caching. Just make sure you've set appropriately high values for shared_bufferes, work_mem, maintenance_work_mem, and effective_cache_size within your postgresql.conf file. Actually, there's a litany of good info in this ...


1

I think I am starting to understand. When I asked you to run SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1; You said it was 2015-07-13 15:11:56 which you have in your WHERE clause When you did the query select sum(diff_ms) from writetest_table; It performed a full table scan of 35.8 million rows. When you did the query select ...


4

For the specific query: select sum(diff_ms) from writetest_table where time_on > '2015-07-13 15:11:56' ; -- use single quotes, not double an index on (time_on, diff_ms) would be the best option. So, if the query runs often enough or its efficiency is crucial to your application, add this index: ALTER TABLE writetest_table ADD INDEX ...


0

The simple occurrence of SOS_Scheduler_Yield don't mean very much. This can have some other causes. Generally you can take a look at this DMV to clarify some things. SELECT * FROM sys.dm_os_wait_stats WHERE wait_type = 'SOS_SCHEDULER_YIELD' Interpretation of dm_os_wait_stats: High counter values, low time means everything ok. This mainly just means ...


0

Without having high CPU utilization you can probably just ignore it but watch it when utilization goes high. This most likely means that 'your CPU is under sustained pressure and threads are being scheduled as normal'. SQL OS manages threads in the MS SQL Server app, not Windows OS. SQL OS uses a method where threads are released by the process on purpose ...


0

The parameter is described in the Upgrade Guide. You cannot downgrade a database to a version that is below the version of the COMPATIBLE parameter. The COMPATIBLE parameter controls how data is written to disk. But it does not control the usage of optimizer features. There are a lot of parameter to control the oprimizer features. The ...


2

I have studied your question hard, but can't make sense of the procedure you describe. (You might work on the description some more.) Why would you generate sequence numbers by hand, when you can just have Postgres generate them automatically? Per documentation: If a list of columns is specified, COPY will only copy the data in the specified columns ...


2

Using the MONTH() function on a column automatically disqualifies any index from usage You can state the range in the WHERE clause SELECT s.id, s.player, COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 AND dp.colour > 5 then 1 else null end) as totalwin, COUNT(case when dg.winner = 2 AND dp.colour <= 5 then 1 when ...


0

Your wording isn't entirely clear, but it seems you are asking about what we call a "multi tenanted" design. There are two main ways to achieve this: Have a separate database for each tenant (i.e. each company using your product). This has the advantage of simple security as each client's data is in its own database so bugs in your code can't accidentally ...


0

If I understand your question well, and you want to build a database by making use of a relational database management system (RDBMS), you should create a table for every entity that you have determined as relevant in your scenario, e.g., one table for Company, one for Person (who are Employees of a given Company), one for Project, and one for Task. Once ...


1

Also worth reading: Best Practice in File Storage while Building Applications - Database (Blob Storage) Vs File System BLOB Storage as the Best Solution For better scalability. Although file systems are designed to handle a large number of objects of varying sizes, say files and folders, actually they are not optimized for a huge number (tens of ...


0

The main cost of handling the data is the I/O. You are doing approximately the same amount of I/O, whether it is in 4KB chunks in the OS (plus directory traversal) or 16KB chunks in InnoDB (plus indirect block lookup). The filesystem and InnoDB are cached in radically different ways; this may factor into a difference -- depending on how cacheable the blogs ...


2

Indexes haven't been maintained or rebuilt so there are hundreds sitting at 30%+ fragmentation... this is my initial suspect of massive and constant CPU use... and Can out of date statistics and highly fragmented indexes throughout the DB cause the excessive CPU use? This is partially true. Index fragmentation wont cause HIGH CPU. Internal ...


0

This snippet was pretty telling: -rw-rw---- 1 mysql mysql 18874368 Dec 13 23:03 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Dec 13 23:03 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Dec 13 23:03 ib_logfile1 Looks like you are under constant write load. What are you doing with this db?



Top 50 recent answers are included