Hot answers tagged

22

Not all reads are equal. SQL Server knows that accessing LOB data is expensive, and tries to avoid it when possible. There are also detailed differences in the way the LOB data is read in each case: Summary The numbers are different because: The select reads the LOB in packet-sized chunks The variable assignment test does not read the LOB at all The ...


13

As far as loops go for this type of thing, don't worry about it. Loops and cursors have a bad reputation because there are normally better set-based approaches that are often faster. For admin stuff, sometimes loops are the only way, and no set-based ways of doing this spring to mind, although you can parallelise tasks with tools like start in DOS, SSIS, ...


8

am I correct in thinking that LOB_DATA pages can cause slow scans not only because of their size, but also because SQL Server can't scan the clustered index effectively Yes, reading LOB data not stored in-row leads to random IO instead of sequential IO. The disk performance metric to use here to understand why it is fast or slow is Random Read IOPS. ...


8

Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA). Merely having the XML column in the table does not have that effect. It is the presence of XML data that, under certain conditions, causes some portion of a row's data to be stored off row, on LOB_DATA pages. And while one (or ...


8

You are using a linked server to access Table1 and Table2. The first query is sent as is to the other server and executed there returning only the rows you want. The second query is doing a join between a local table TB_BRANCH and a remote table Table1. To do that it fetches all rows from Table1 and all rows from Table2 to your local server and does the ...


6

You are probably running into parameter sniffing issues. The fact that you execute the query with a different ARITHABORT setting makes SQL Server create a new plan and not reuse the existing plan as that setting is part of the cache key. Have a look at Slow in the application, fast in SSMS, it has a lot of information such as this: We have seen that ...


5

Here's one method that doesn't require a looping construct: DECLARE @sql nvarchar(MAX) = N''; SELECT @sql += N'ALTER DATABASE ' + QUOTENAME(name) + N' SET RECOVERY FULL; ' FROM sys.databases WHERE database_id > 4 AND name NOT IN ( N'distribution', N'SSISDB' ); PRINT @sql; --EXEC(@sql); I agree with wBob's answer in that one need not be ...


5

This is too long for a comment so I'll chime in with another case where one might want to turn off auto update stats. I've worked with databases supporting high-volume OLTP workloads and a stringent query performance SLA in milliseconds. Nearly all queries were trivial with a lot of attention to query and index tuning detail and some of the tables were ...


4

...once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds. This seems to indicate that the storage subsystem is inadequate. If this is the cause, SQL Server will probably be waiting with one of the PAGEIOLATCH_XX wait types. It definitely appears that the LOB data must be loaded into memory on the ...


4

You are correct, I also believe that in most cases the Auto Update statistics should be set to true we should allow SQL Server to decide when to update stats and believe me it does good job. When this is set to true it make sure stats are updated about distribution of data in the field which would eventually help optimizer to prepare better plan. The ...


4

Generally I would say that having auto update statistics on is beneficial. But like any setting, there are reasons you can turn it on or off. One is that some tables have a lot of churn, and perhaps queries are not very sensitive to accurate statistics. Think ETL or other bulk scenarios where you're changing a lot of data, but either not reading it from ...


3

Please look at the three main queries in the following answer (of mine): SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type The first query looks at currently running queries. The second two queries (labeled "Query #1" and "Query #2") work in combination to find high-cost queries over a period of time.


3

With schemabinding can improve performance in some cases. I couldn't recall all the details, but found an excellent blog post that goes through this in detail: With user defined functions that may need to update data. You might benefit from improved performance that would prevent an Eager Table Spool. SQL Server SCHEMABINDING Edit: Paul White ...


3

If the reporter had any thoughts of rigor I would expect the hot cache / cold cache information to be included in the report. Otherwise it is like saying a car costs $y without saying which option, warranty, service or taxes that includes. Similarly I would expect to see it stated that subsequent tests were performed under the same conditions and how that ...


3

There are a number of different factors involved but it is entirely possible to have a replica that is synchronous be faster or slower than an asynchronous one. To list a few: Replica is under too much load Hardware specification differences (i.e. synch runs ssd and async runs rotational) or synch is using 8 cores @ 3.6 GHz and async is using 4 cores @ ...


2

The Brent Ozar Unlimited First Responder Kit is a good and free place to start. It will walk you through looking for slow queries and comes with a handy worksheet of what to do.


2

UPDATE: What Worked... So after reading the link from @Raj and reading @ora-600's answer I tried to validate the database with the RMAN command backup check logical validate database;. While this worked fine, it was also clear that it was not looking at everything that the ANALYZE INDEX command would. After trying many different variations, I finally ...


2

I think the article Raj quoted (https://www.pythian.com/blog/analyze-index-validate-structure-dark-side/) describes this pretty well. "clustering factor" was also my first guess while reading the description of your problem. I also prefer to use RMAN to check for corruption. RMAN> backup check logical validate database; Afterwards you can query ...


2

I'm troubleshooting an issue with one of our SharePoint databases, essentially huge drops in PLE which I think are caused by increased I/O activity (I see increased reads, writes and lazy writes at the same time) I can see from the question that you have SQL Server 2012 SP1. There was Bug in SQL Server 2012 which forced PLE to plummet but that was fixed ...


2

That depends on your disk subsystem. A full backup is going to involve a metric crud-load of I/O (that is a technical term). That is going to put a huge load on your I/O subsystem. Depending on how I/O bound your system is, how much spare bandwidth your I/O subsystem has, how fast your I/O subsystem is, etc. the impact will range from "yeah, maybe it's a ...


1

This is a bit of a fishing expedition but here would be my troubleshooting process assuming you don't have your own stats to help clear things up. Check the history of database size and compressed backup size from your msdb catalogs. Have they changed over the past few weeks. Sometimes an application will go bezerk and fill a database with garbage; what ...


1

It depends on the activity on the table. It there is no activity then there should be no difference.


1

There seems to be a bug in MySQL (checked up to 5.6.21 on sqlfiddle.com) where the optimizer does not identify the optimal index when it is available. It can be worked around by using force index() hint: SELECT post_modified_gmt FROM ( SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt FROM (SELECT @rownum:=0) r, wp_posts FORCE ...


1

So initially it's T2[ id PK, name, T1_id NOT NULL, -- because it is hierarchy, T2 can not exist outside the parent (T1). FK1 = T1_id ref T1(id) ] ... and you also say LINK_T1_T2_XYZ[T1.id, T2.id, XYZ.id] with a check T1 should parent of T2 You can migrate keys down the entities hierarchy T2[ id, name, T1_id NOT NULL, ...


1

First off, if your example is ACTUALLY a good representation of what you are trying to accomplish, then you are going about it all wrong.. You have three domains here - one is businesses, one is business offices, and one is products. Table BUSINESS should only contain information about the actual business (Netflix, for example) ... no office information at ...


1

As long as the developers only have that role in the development database instance, I see no problem; far better to tune now than later. Now, if your development machine has limited resources, there may be a reason to limit access. But in general, usually tuning tasks run from a few minutes to the default maximum of 30 minutes; during that time, Oracle may ...


1

You don't need loops, but one CASE per searched value: select ... from ( select ... case when 'Bach' IN (col1,col2,col3,col4,col5) then 1 else 0 end + case when 'Joan' IN (col1,col2,col3,col4,col5) then 1 else 0 end + case when 'Mike' IN (col1,col2,col3,col4,col5) then 1 else 0 end as matches from tab ) dt where matches > 0 ...


1

P_S in MySQL versions below 5.7.4 doesn't collect information regarding executed prepared statements: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html. Maybe your application is using prepared statements instead of simple ad-hoc statements (parameters concatenated). Some client libraries, like PHP's PDO can avoid this (MySQL's) issue by ...


1

If you lump all the column_## columns into a JSON field, you can have a limit of 4GB. The question of how much gets complicated. InnoDB has 4 ROW_FORMATs: REDUNDANT, COMPACT, DYNAMIC, COMPRESSED. I think you could have 400 columns of VARCHAR(255) or TEXT (or ...) in ROW_FORMAT=DYNAMIC. That format uses a 20-byte pointer in the record to where the actual ...


1

Since you are running SP3 in QA and SP1 in production (assuming you are running with Trace Flag 4199) you are missing out on a lot of performance related fixes in your production environment. My first suggestion would be to test the query and compare the generated plan on servers of the same build number. Without details of the query and the underlying ...



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