New answers tagged

0

MDS has been well embraced in production. Where it excels is for data storage where users need to see and edit the data. The alternative is to build a database schema and then a matching client or web app in something like Microsoft LightSwitch. MDS can be done in an hour. This latter one is weeks of work possibly across two people. I've seen MDS used for ...


1

Hard to say for sure without seeing the full schema and queries, to work on the execution plans. But you could try: Where (Select Count(Distinct fld1) From t1 ...etc... where ...etc... and fld1 In ('X', 'Y', 'Z')) = 3


0

How many milliseconds (ms) does the query take to run in Query Analyzer? Did you set showplan on yet and analyze it? Have you tried performance monitoring on the box to track CPU affinity, memory usage, locks, disk usage etc...? Are there other SQL transactions running when you run this process? Is the data and indexes spread out on the SAN/NAS/DAS? How much ...


3

You might have to play with CHAR(13) and CHAR(10) depending on the source of the text, but this might be simply: WHERE CHAR(10) + col LIKE '%' + CHAR(10) + '[A-Za-z].%' ------^^^^^^^^ in case the text *starts* with a lettered list Note this will also find: Some sentence is here a. foo Some more text later If you need to only return rows where there ...


2

The time is not stored as number of milliseconds. It is a numeric representation of the actual time. For instance, 12:06:59 is represented as 120659. 1:00:02 pm is presented as 130102. 1:15:42 am would be 11542. Instead of calculating that all yourself, simply use the dbo.agent_datetime function. Something like: SELECT ...


1

I too was experiencing a 5 to 10 second delay when right-clicking a table to pull up the context menu. My situation might be a little different than some however because I use local databases only. My solution: After reading the answer from imran about disabling antivirus, I took that concept a step further since I do not want to disable my antivirus ...


1

To answer your question simply, if you have 1 VM with several instances, you have the possibility of resource contention between the instances on that VM. In that scenario, I would consolidate databases onto a singular instance. But if you're talking about multiple virtual servers with one instance per virtual machine, I'm going to go against the grain here ...


4

You need to escape the single quotes in the parameters, like: exec (' bulk insert weblog from '''+@datapath+'access1.log'' with ( firstrow = 1, FIELDTERMINATOR ='' '', ROWTERMINATOR=''\n'', MAXERRORS=99999999 ) '); Use the PRINT command to see what the command contains prior to EXECing it: DECLARE @datapath VARCHAR(260); SET @datapath = ...


2

assuming licensing is not an issue, security and compatibility can be easily grouped and managed (very small user base, very small lightweight databases) You already have addressed the concerns. IMHO, depending on the VM capacity (memory, CPU and disk space), you should consolidate databases to one instance. This gives you advantage of managing one ...


3

In and of itself, no, you are probably not adding a security risk. As long as no one but a sysadmin can alter the procedure and as long as the ONLY thing the procedure can do is start a job I don't see a problem. The risks come in with the fact that MSDB is (and is supposed to be) marked as TRUSTWORTHY. That means that by using EXECUTE AS OWNER you are ...


0

I've done that but I've still sometimes had it prompt to reinitialize the whole thing. The snapshot will only contain the new table that has been added. Also, we frequently remove a table from replication but need to add it back in later, are there scripts out there to preserve the replication settings so this process is easier? Script out ...


0

Speaking as someone who has taken over from multiple third parties who have done NetBackup database backups you can safely assume three things. Many of your servers and individual databases have not been backed up. You would do best to confirm yourself through msdb checks. The "tapes" being backed up to are probably not accessible, do not work, or ...


2

For the 'good' plan, all the table variable cardinality estimates are 1 row. This is the most common outcome when using table variables, unless trace flag 2453 is enabled, or a statement-level recompilation occurs (for example because OPTION (RECOMPILE) is used, or one of the regular tables in the query has passed its recompilation threshold. For the 'bad' ...


2

The issue with your current query is that you are only looking for queries that could be running at that very moment. To get a good full picture of your server state, you could try to run SQL Server Profiler or check for connections with a built-in stored procedure likesp_who. These approaches are more complicated and time-consuming than using a stored ...


5

Since you are running Microsoft SQL Server 2014 - 12.0.2000, the very first RTM build including the new Cardinality Estimator I would strongly suggest you try updating to one of the latest CU's. As stated in this blog post on msdn You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix. SQL Server 2014 Service ...


2

We solved the issue. The issue was caused by the new Cardinality Estimator in SQL 2014. We disabled the new estimator by activating trace flag 9481 and removing the query plan from the cache then the query worked again.


4

DB_NAME does not work as advertised before SQL Server 2016 (where the behaviour of DB_ID is also changed). For details, see: Information disclosure with the db_name and db_id function (Connect bug report) There is a similar situation with other metadata functions, including: suser_name suser_sname suser_sid user_id database_principal_id is_rolemember ...


1

Your comment confirms that the original long script was using variables and these have become parameters. Probably you are benefitting from parameter sniffing here and better execution plans. The values for variables are not sniffed unless you use the ’option recompile' query hint so you will get the same plan regardless of what the runtime values are. ...


2

How do I convince SQL Server that the function is nondeterministic and make the function evaluate for each row, computing different hash values? SQL Server marks the function nondeterministic, because it accesses data in system tables: SELECT UserDataAccess = OBJECTPROPERTYEX(FN.ObjectID, 'UserDataAccess'), SystemDataAccess = ...


0

Just solved that issue myself, your SQL server is missing a few features that's why SSISDBBakup.bak file is missing, it should be under: C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak Install SQL Integration Services Feature and it will work. /Burim


5

If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower? It depends. No - if you run exactly the same queries as before (no access to the new data at all). SQL Server's partitioning implementation creates a separate rowset for each partition, so when you create a partitioned index, it creates a separate b-tree ...


1

I found that your query had many redundancies in the conditions, and you used cross joins that were good candidates for simple joins. This might confuse the planner. Perhaps you could try the following rearrangement of the query (it is functionally exactly the same but uses joins and removes all the redundant comparisons) to see what the planner comes up ...


1

If you check out the Microsoft Developer Network (MSDN), they have good documentation and links for downloading all the versions, including future version previews. Additionally, there are links for tutorials, text references, as well as various other resources you'll find useful throughout your learning process. Here is the link: ...


6

No, the SPID (Server Process ID) is assigned the moment an application establishes a connection/session to the database and is retained until the connection/session ends.


2

I ended up with an approach that yields the optimal solution in this case and I think will do well in general. The solution is quite lengthy, however, so it would be interesting to see if someone else has a different approach that is more concise. Here is a script that contains the full solution. And here is an outline of the algorithm: Pivot the data ...


1

If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower? Wrong question. Yes, they will be slower - the index is deeper, more data must be accessed to filter. But the real question is: Will it be significantly or at least noticeable slower - and that is likely a no, because the index depth growth is NOT linear ...


6

That DONE_IN_PROC message from the execution plan is completely independent of your WHILE loop, and isn't what's being checked in your conditional BREAK. Martin deleted his answer but this is very much by design. Your query shouldn't behave differently depending on whether or not your client tool is retrieving execution plans - that would be bad. Execution ...


0

We ended up coming up with a solution for this. Basically, we had to manage the partition creation for both the publisher and subscriber databases/tables when we made a change. We were loading data using SSIS and there were mechanics that were built in to create new partitions on the publisher, so we implemented the same logic to do the work on the ...


0

Thanks everyone for the comment. I decided to look at the sys.sysprocesses table and found that FT_CRAWL (looking in the CMD field) was running on the blocking spid. I guess a restored DB starts crawling right away? Anyways, I shut off the Full Text Daemon and the blocking ceased. We finished the upgrade and then turned the Daemon back on and all was well in ...


24

This is very easy to prove on your own. We can create a table with a computed column that uses a scalar user-defined function, and then check plans and function stats before and after both an update and select, and see when an execution gets recorded. Let's say we have this function: CREATE FUNCTION dbo.mask(@x varchar(32)) RETURNS varchar(32) WITH ...


10

It depends on how you define the computed column. A PERSISTED computed column will be calculated and then stored as data inside the table. If you do not define the column as PERSISTED, it will be calculated when your query is run. Please see Aaron's answer for a great explanation and proof. Pinal Dave also describes this in detail and shows proof of ...


1

In your first query, in the where clause, you're restricting results to those where y.TransType = 'used'. This turns the left join into an inner join because you're throwing away results where y.TransType is null (aka where the other ticket doesn't exist). An easy fix for this is to move that condition to the on clause like this: select x.* from factI as ...


1

From the docs The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. This dmv is therefore reset for each stored procedure recompile, which can happen, for ...


10

Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause. Your second query can be rewritten without derived tables by simply moving that condition to the on clause: select x.*, y.* from factI as x left join factI as y on x.tickedId = y.tickedId and ...


1

This one uses a recursive CTE. Its result is identical to the example in the question. It was a nightmare to come up with... The code includes comments to ease through its convoluted logic. SET DATEFIRST 1 -- Make Monday weekday=1 DECLARE @Ranked TABLE (RowID int NOT NULL IDENTITY PRIMARY KEY, -- Incremental uninterrupted sequence in the ...


1

For the sake of completeness, here is a two-pass gaps-and-islands approach that I tried myself before asking this question. Update: As I was testing it on the real data I found few cases when it was producing incorrect results and fixed it. Here is the algorithm: Generate islands of consecutive dates (CTE_ContractDays, CTE_DailyRN, CTE_DailyIslands) and ...


2

Yes you can look in sys.types for such a list or use the type_name function to look up an individual type_id.


0

What I did is I used the following query to create a table with each of the data types. This is the same table that was used in the implicit conversion test that was used on SQLSkills.com. CREATE TABLE dbo.DataTypes ( RowID int NOT NULL IDENTITY (1, 1), BigIntCol bigint NOT NULL, BitCol bit NOT NULL, CharCol char(10) NOT NULL, DateCol ...


-3

WITH Grouped AS ( SELECT D.bank_id, grp = D.bank_id - ROW_NUMBER() OVER (ORDER BY D.bank_id) FROM banks AS D WHERE status = 1 ) SELECT MIN(Grouped.bank_id) AS start_id , MAX(Grouped.bank_id) AS end_id FROM Grouped GROUP BY Grouped.grp start_id end_id ----------- ----------- 1 6 8 14 16 ...


0

What build of 2008 r2? Have you looked at this connect? https://connect.microsoft.com/SQLServer/feedback/details/672153/the-ole-db-provider-sqlncli10-for-linked-server-x-reported-a-change-in-schema-version-between-compile-time-and-run-time Have you tried the workaround listed? Workaround (From above connect): I solved my problem with DBCC ...


3

To start with I must say you have set max server memory to 6 GB and total memory is 8 GB so you have just left 2 GB for the OS, which in many cases, even if nothing is installed apart from SQL Server on a Windows machine, is too little memory provided to OS. To function properly, on a system with antivirus installed, OS must be given 4 GB at least. I leave ...


1

It sounds to me like you are suggesting that you require a highly available file share (see https://blogs.technet.microsoft.com/clausjor/2012/06/07/smb-transparent-failover-making-file-shares-continuously-available/). In order to do this you will obviously need Clustered storage (an ip and network name). It would be a little unusual to do this using your ...


3

Referenced/referencing entity functions return instances where an object, column etc. is referenced directly (usually in other objects like stored procedures, but Mark has the full list from the documentation). These functions are not meant to analyze foreign key dependencies, which you can get from the foreign key catalog views. This simple query assumes ...


2

According to BOL: A table is tracked as a referencing entity only when it references a Transact-SQL module, user-defined type, or XML schema collection in the definition of a computed column, CHECK constraint, or DEFAULT constraint. Foreign key relationships would not show in this DMV.


4

Based on your sample data it looks like there are 6 columns in total. COL1, COL2, COL4, COL5, COL6 = All fixed width . COL3 = This is a fixed width for a given day, but it is variable day to day (based on my understanding). You should be able to calculate COL3's width each day based on the length of any given line. You know COL1,2,4,5,6 are all fixed ...


4

Not exactly what you are looking for but could perhaps be of interest to you. The query creates weeks with a comma separated string for the days used in each week. It then finds the islands of consecutive weeks that uses the same pattern in Weekdays. with Weeks as ( select T.*, row_number() over(partition by T.ContractID, T.WeekDays order by ...


0

Unlike sysadmin which bypasses checks, the built in database roles aren't so special that they can't be overridden with a DENY. Try looking at Exec sp_helpprotect Null, 'Username' and seeing what DENY records show up.


0

I finally found the options of the database in Sybase Central and had a play with various options and I got it to work with turning ON "allow_snapshot_isolation" it was OFF by default.


1

I stumbled upon the same question a couple of years ago when investigating performance issues in Dynamics AX. Following Microsoft's explanation, I could see a different execution plan triggered by the hint and this corresponds to the other answers given here. With the hint, SQL Server tries to find an execution plan that allows a sort of streaming of ...


11

If a non-clustered index is created on a partitioned table, that index will be partition-aligned by default unless you explicitly specify that it should not be (e.g., use ON [PRIMARY] to specify the file group). In such a case, the SSMS script index functionality will not show the partition scheme used, but you can use sp_help on the table to confirm that ...



Top 50 recent answers are included