Tag Info

Hot answers tagged

20

There are three main technical reasons you get the plan you do: The optimizer's costing framework has no real support for non-inline functions. It does not make any attempt to look inside the function definition to see how expensive it might be, it just assigns a very small fixed cost, and estimates the function will produce 1 row of output each time it is ...


18

It looks like this is a cost based decision by the optimizer but a rather bad one. If you add 50000 rows to PRODUCT the optimizer thinks the scan is too much work and gives you a plan with three seeks and one call to the UDF. The plan I get for 6655 rows in PRODUCT With 50000 rows in PRODUCT I get this plan instead. I guess the cost for calling the ...


4

Today, without time, is: DECLARE @d SMALLDATETIME; SET @d = DATEADD(DAY, GETDATE(), DATEDIFF(DAY, '19000101', GETDATE()); -- when you move past SQL Server 2005, which you should, you can use the much tidier: -- DECLARE @d DATE = SYSDATETIME(); Then to move to the first day of this month: SET @d = DATEADD(DAY, 1-DAY(@d), @d); Now to get all employees ...


3

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index. Incorrect. An unfortunate overload of the term 'snapshot'... A snapshot read of the index is used, which means row-versioning see How Online Index Operations Work: A snapshot of the table is defined. ...


3

SELECT A.PersonName, A.Email FROM ( Select Person.PersonName, Email.Email ,ROW_NUMBER() OVER(PARTITION BY Person.ID ORDER BY Email.Email) AS RN From person left join Email on Person.ID=Email.PersonId ) A WHERE A.RN = 1


3

I would suggest automating generation of the restore script. There are a few versions of this out there but Paul Brewer has a T-SQL and PowerShell version available. I have seen some setups as well that just include generating a restore script when the backup is accomplished. Which, just a note that a new open source backup solution was released by Sean ...


2

As it does not matter which email shows up. I think that the following one is very direct. Select Person.PersonName, MIN(Email.Email) From person left join email on Person.ID=Email.PersonId group by Person.Id, Person.PersonName


2

I would us an outer apply for this, I find it more readable. Select Person.PersonName, coalesce(Email.Email,'No email found.') as Email From person outer apply ( select top(1) Email.Email from Email where Person.ID=Email.PersonId order by <whatever suits you> ) as Email;


1

This is due to the fact you reached the row size limitation. But this should affect your production server too? Are you sure that both server are the same and both server use the same table design? You can try to use nvarchar(max) instead of (e.g). nvarchar(250) for testing purposes. This will use more space and will be slower, but will avoid the 8060 ...


1

There can be many reasons for that. You're queries run and generate a version, during another query use the same tables. During this phase version 1 will be hold back. If your transaction isn't commited at the end, it may occur that version 1 will last for a long time in your tempdb. Make sure that all transactions are probably commited and closed. Make ...


1

select P.PersonID, (SELECT TOP 1 E.Email FROM Email E WHERE E.PersonID = P.PersonID ORDER BY <pick your column here>) from Person P


1

Even though this is a pretty old thread, Microsoft has officially published the list of Trace Flags that can be used when running SQL Server 2012 and up for high performance workloads : KB - 2964518 - Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads. Its highly advisable to do a thorough ...


1

My understanding is the snapshot should reside on the index file drive. Is this correct? It resides on same file where Index file resides unless you use Sort_in_tempdb option during rebuild. From BOL When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup. If the SORT_IN_TEMPDB option is set to ...



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