Tag Info

Hot answers tagged

8

This is most easily implemented in SQL Server using a Recursive Common Table Expression. Table definition DECLARE @binaryUser AS TABLE ( id integer NOT NULL, joiningDate date NOT NULL, placement char(1) NOT NULL, pId integer NOT NULL, cId integer NOT NULL, referBy integer NOT NULL ); Data INSERT ...


6

The following assumptions have been made (some of them possibly repeating parts of your description): SF/FLEX is always followed either by a number immediately or by a space character and then a number. There is always one space character before SF/FLEX, unless the item is at the beginning of the string. There is always a comma or a space after the number ...


5

There is an easy solution if you have Enterprise (expensive) Edition in use: Set maxdop at instance level as high as you want and use the resource governor to restrict all CRM users with their own workload group to maxdop 1.


4

There is no hidden impact in raising the cost threshold for parallelism. As low as it is, SQL Server ships with a default cost threshold of 5, which is always taken into account when the optimizer compiles query plans. So, in this regard, the amount of work for the optimizer is the same. Whether raising the CTFP to a very high value is a good idea or not, ...


4

For Microsoft Dynamics CRM database server instance, you have to set MAXDOP 1. If using Enterprise edition, above answer using Resource governor makes sense. You can also look into using query hint OPTION (MAXDOP N) where N is a sensible value calculated based on number of logical processors and NUMA nodes of your sql server instance. Make sure that ...


3

This is what I use to monitor sleeping SPIDs causing blocking issues: SELECT ,s.session_id ,s.status ,s.login_time ,s.host_name ,s.program_name ,s.host_process_id ,s.original_login_name ,s.last_request_end_time ,CAST(t.text AS nvarchar(4000)) AS [text] FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_connections AS ...


2

You can clear the log during the REORG process as its not done as a single transaction. The log bloating depends on index fragmentation level and amount of work done. Again, REORG is a fully logged operation on all recovery models. https://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx


2

Is it possible to redesign your schema? It feels like you are making life harder for yourself by basically trying to pivot the data you're importing from the excel spreadsheets. CREATE TABLE dbo.Hardware -- hw? ( [Event_Num] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(100) NOT NULL, [Install_Date] DATE NULL, -- Install after pulling? ...


2

I just quickly grabbed your query and re-wrote it without looking at how you rewrote it. SELECT DISTINCT a_stmt.cyc_dte, a_carr.carr_nm, a_stmt.amt_du_ic FROM a_stmt LEFT OUTER JOIN a_icc on a_stmt.ic_cntct_id = a_icc.ic_cntct_id INNER JOIN a_carr on a_carr.acna = a_stmt.acna INNER JOIN s_dataccs on s_dataccs.acna = ...


1

It might be related with leaking connections exhausting the connection pool. If the connections are not released properly they will remain active and will not be returned to the connection pool until the next Garbage Collection executes. If a lot of connections are opened in a short time the connection pool might fill up before the GC runs, and the following ...


1

If you're comfortable with Data Collector, you could download and install ExtendedTSQLCollector and set up a collection set to capture Blocking and Deadlocking events with XE. I have a blog post that explains how to do that. In order to collect sleeping SPIDs that cause blocking, you could set up another collection set (or an additional collection item in ...


1

Mirroring being dropped in future version is an annoyance to many who can't use AO instead (mainly due to the enterprise edition requirements), but if you need to use it you'll find it present and supported as far as SQL2014 (in MS rlease terminology deprecated means "we intend to remove it at some point, so be prepared to switch sooner rather than later" ...



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