Tag Info

New answers tagged

0

The following is kind of a shot in the dark in lack of clarification of the question, but as I had built it already earlier I'll post it in absense of any other answers. How about implementing the message-deletion-per-recipient so that you only keep track of those messages that have been tagged as deleted-by-recipient? That would translate into getting rid ...


0

Except for the general log and the slow log with very small long_query_time, performance shouldn't get affected too much when on, as most logging should be written to the filesystem cache (those logs do not need to be transactional safe). There are certain combination of configuration and workloads that certainly can affect negatively performance: ...


1

The parameter INTERNALLENGTH is only applicable to the creation of a new base type, which is a rather specialized operation for advanced users. It would require to provide input and output function etc. What you display is the creation of a new composite type, which is a more common operation. There is no parameter INTERNALLENGTH for that purpose. Read the ...


0

there are no queries in the slow log Then, make sure they will be. One of the easiest ways to profile MySQL queries is through the slow log. if you find no slow queries is because you probably have a lot of them that take lower that 10 seconds to execute, or that are locked waiting (that time is not considered, "execution time"). Set long_query_time to ...


1

First, you need to know what you are doing to InnoDB when you plow millions of rows into an InnoDB table. Let's take a look at the InnoDB Architecture. In the upper left corner, there is an illustration of the InnoDB Buffer Pool. Notice there is a section of it dedicated to the insert buffer. What does that do ? It is ised to migrate changes to secondary ...


1

I wanted to write a comment (as this is not a definitive answer), but it became too long: I am going to give you several broad pieces of advice, and we can go into details for each one, if you want: Reduce durability (you have already done some of it). Latest versions allow even doing it more. You can go as far as disabling the double write buffer,as ...


1

Define your stored procedure to take a table valued parameter (TVP). The TVP will have two columns, one for ID and one for OrderSequence. Populate this TVP in your application, setting just the IDs you wish to amend. The SP can perform an UPDATE by joining to the TVP. This is more efficient than a cursor or multiple SQL calls.


0

MyISAM is the worst offender for updating individual rows on concurrency. First thing you want to do is use InnoDB to be able to execute UPDATEs in parallel. InnoDB may have lower insertion rate, but that can be solved by reducing its durability setting (innodb_flush_log_at_trx_commit = 2). That, combined with a large enough buffer pool, will make most of ...


0

Use the maxmemory to set a limit to how much your Redis database can grow too. Failing to do so, Redis will grow until the OS will kill it once memory is exhausted (per your current experience). The usage of maxmemory should be coupled with maxmemory-policy - you can choose from different eviction policies depending on your use case's requirements. For ...


1

You can accomplish this using ROW_NUMBER() syntax, as mentioned by @Kin : USE Tempdb; CREATE TABLE dbo.testRowNum ( ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) , Name VARCHAR(255) , SequenceNum INT NULL ); INSERT INTO testRowNum (Name) VALUES ('A'), ('B'), ('C'); SELECT * FROM dbo.testRowNum; UPDATE dbo.testRowNum SET SequenceNum = ...


1

When I queried sysprocesses to see what the spid was doing, it had FT BATCH CMPLETE in the cmd column. Does anyone know what this command is? It is related to Full text catalog. You can find out more info using sys.dm_fts_outstanding_batches e.g. from Pro Full-Text Search 2008 book, Below will give you the Number of Full-Text Index Population Batches ...


0

To find out more about the PREEMPTIVE_COM_SEQSTRMREAD wait type I would read this great article at sqlauthority, but as Shanky said in the comments, SSIS would more than likely serve you better for a large migration.


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


25

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


0

QP metrics are always captured in the default group 1 in each respective database and you can access the metrics info using sysquerymetrics view. So I would suggest you to backup the metrics using sp_metrics 'backup' from the default running group to a backup group Then drop the metrics using sp_metrics 'drop', '@gid' so to drop all the metrics from ...


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

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


-1

This question is a hardy perennial on the Oracle lists. The PIVOT operator only came in in 11g, so that's no good to you. You might start by looking here (also gives non-PIVOT solutions - oracle-base is an excellent tips and snippets site). The Oracle forums FAQ has a "How do I convert rows to columns" section here. An interesting application of windowing ...


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



Top 50 recent answers are included