Hot answers tagged

16

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong? At the least it is incomplete and potentially misleading advice (I didn't bother to read the whole article). If you're going to read stuff on the Internet (including this), you should adjust your amount of trust according to how well you already know and trust the author, but always ...


10

I wonder if this imbalance of the number of rows maybe a result of the Timeout Almost without exception, no. An initial cardinality estimation is performed before optimization begins. Subsequent optimizer transformations may require new estimates to be computed. There is no general rule to say which of the estimates will be more "accurate". See this ...


6

To understand the jargons, Aaron Bertrand has a #BackToBasics blog post Also, refer to this decision matrix for TLS 1.2 support or MS KB - TLS 1.2 support for Microsoft SQL Server 10.50.6220 => SQL Server 2008R2 SP3 GDR 10.50.6529 => SQL Server 2008R2 SP3 QFE From the decision matirx, if you dont want TLS 1.2 support, dont install 10.50.6542. TLS 1.2 ...


6

I think this will get you what you want. It creates a recursive CTE (common table expression) that generates all the dates between January 1 and March 1. It then joins those dates to the tool rental data, checking to see if each date record falls between the rental dates. This gives you one record per toolid for every day within the range of rental ...


5

Instead of doing an expensive CROSS JOIN plus aggregating a potentially large number of rows you can use this approach: SUM the days including the duplicates and subtract the number of rows with duplicate dates. ;with cte as ( select *, row_number() over (partition by ToolId order by StartDate, EndDate) as rn from ...


5

You can avoid the expensive recursive CTE by using a date table. These are easy to construct and very useful. I used a variation of the code here to generate one. Note: I'm using a temp table for the date here. This is just for the demo code. The permanent solution should use a permanent date table. It should probably also include some/all of the ...


4

Without further info, this is more of speculation but judging on what we have: a table that is quite wide (1.3 to 4.0 rows per page on average) the query that is slow is using: only PWFID on the join condition, two columns Title, SITime on the select list and no other column anywhere (WHERE, HAVING etc.) Then a covering non-clustered index on ...


4

Sure, you can generate the list of years in dynamic SQL, but I suggest passing them as numbers (just one less thing that can go wrong if people pass in bad data, and I'm sure you didn't really mean to hard-code the values inside the procedure, defeats the purpose): DECLARE @year1 smallint, @year2 smallint SET @year1 = 2014; SET @year2 = 2016; DECLARE ...


4

Will changing isolation level to Read Committed Snapshot Isolation (RCSI) help here? This is not a straight forward change and it comes with additional tempdb penalty. I would not suggest you to just change the isolation level to RCSI without properly testing and seeing benefits in your environment. Trust me, this is a sledge hammer approach. We ...


3

Ensure you have a valid backup; hopefully it will be from prior to the corruption, but not so long ago that the data isn't useful. You should set this aside in case direct repair isn't possible and you need to recover data from the backup. The documentation explains what to do to correct the problem - you can try the REPAIR_REBUILD option, and if that ...


3

We now have a table with over two billion rows and 700GB of data. It's a small number of columns, one being a 256 char varchar used to store serialised blob data. This is now of a size that when people try to insert, delete and update to it (via the app) users occasionally lock each other out. This can only get worse as time goes on. Why do ...


3

Here is a starting point. The first query will give you a list of roles with either INSERT or UPDATE permission. From there you will need to look at the permissions being at the DATABASE, SCHEMA, or OBJECT level and decide if they cover the objects you are interested in. Check the state_desc column to make sure it is GRANT (it probably will be) as DENYs ...


3

Well, I did something similar to Steve, but with no need for a #temp table or recursive CTE. DECLARE @BigString nvarchar(max) = N' /* CREADO POR : Wxxwww wwwwww */ /* FECHA CREACIÓN : 10/12/2015 */ /* DESCRIPCIÓN : ...


2

I've used Openrowset a number of times for this task. This code will create a table in SQL. SELECT * INTO EXCEL_IMPORT FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Excel\Spreadsheet.xls; HDR=YES; IMEX=1', 'SELECT * FROM [Sheet1$]'); Ideally you want to create the table first and then use INSERT INTO instead of the SELECT INTO. ...


1

Your plan sounds OK to me. I have written a detailed answer describing each step at upgrading from lower version of sql server to higher version. To help automate most of the process use PowerShell dbatool written by Chrissy LeMaire [migrates over 25 components including databases, jobs , logins, etc]. Here is a small video as well to get you started and ...


1

In order for SQL Server services to start as part of a Windows Failover Clustering Service, quorum needs to be attained by at least one node in the cluster. If a two-node cluster has one node down, that node cannot contribute to the quorum, so some other method is necessary for the node that is up to attain quorum. Quorum is essentially a set of votes to ...


1

Put this at the top of your script: Add-Type -AssemblyName System.Data Yes it's part of the OS, and no this is not normally required, but there must be some issue from the combination of your OS version + PowerShell version + SQL 2008 R2.


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

I didn't spend a lot of time on performance with this one, but assuming you are ready in some kind of header info, this code should work for you. Parse the string out lines based on a line feed (dump to temp table) Find the largest line with the pattern you are looking for. Read the string from the previous end line position. Code: DECLARE ...


1

Because SQL Server released the memory back to the OS, this means the memory pressure was external, so you aren't going to find the reason within SQL Server. Your answer is going to come from outside SQL Server and I know of one tool that can track the memory allocations of the OS. Rammap https://technet.microsoft.com/en-us/sysinternals/rammap.aspx



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