New answers tagged

-1

This worked on Sql Server and Azure SQL (PaaS) environment. `ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups] ADD SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_SysStart DEFAULT DATEADD(second, -1, SYSUTCDATETIME()), SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT ...


1

I'm not sure how to deal with the bad plan issue, maybe someone will come along with better query tuning skills to help out with that. However, to speak to the tempdb contention issue, the page that all these sessions are fighting over is a PFS page. These are defined in the documentation as: Page Free Space (PFS) pages record the allocation status of ...


7

When accessing UNC paths, or any 'location' that isn't a local drive (i.e. registry), from a PowerShell job step, you need prefix the path with Microsoft.PowerShell.Core\FileSystem::. This tells SQLPS which provider to use, which isn't required in normal PowerShell but is required in the SQL Server implementation. Alternatively, you can change directory ...


2

You can leverage PowerShell and the Thycotic REST API to fetch your credentials from Secret Server programmatically. Use "Windows Authentication" to avoid having to store credentials in the PowerShell scripts, or enter them during runtime. If your deployments run under a build agent service account context, then you need to ensure that service account has ...


1

Try this: $cred = get-credential $cred | Export-CliXml -Path C:\Temp\Mycreds.xml Get-Credential will prompt for the username & password & the Export-CliXml will export the results to an XML file. If you look at the username it will be in plaintext but the password will be encrypted. When the username & password is needed, import the XML: $...


-2

sqlcheckpoint, Please take a look at the link below, which describes which Powershell modules are used by SQL Agent. Its either SQLPS or SqlServer depending upon your version of SQL Server. https://docs.microsoft.com/en-us/sql/powershell/run-windows-powershell-steps-in-sql-server-agent?view=sql-server-ver15 It quite likely the New-Item cmdlet is not found ...


5

As well as writes to tempdb often not every hitting disk/network IO, as extended upon in David Browne's answer, depending on your IO configuration you may find that even when the data is big enough to have to be spooled to disk it is still faster than selecting into a "normal" table: TempDB may be on different drives, so have its own IO bandwidth. This is ...


0

To anyone, that may come across this problem: The issue was nested dependencies. You need to run sp_refreshsqlmodule starting from the bottom of the dependency hierarchy. If you try to refresh the Stored Procedure, that uses this table type and passes it further, you will get this operand type clash error


44

writing to tempdb is faster than an actual table not in tempdb It's true. There are two IO enhancements in TempDb. Writes to a table in a user database must have their log records flushed to disk on commit, or if a minimally-logged insert (like SELECT ... INTO), must have the database pages flushed to disk on commit. The way the minimal logging works ...


1

If the CPU utilization is being measured from inside the VM it’s measuring what percentage of the time its threads are scheduled on the virtual cores. But it doesn’t mean that it’s scheduled on a physical core on the Hypervisor host for all that time. On a physical server, or a VM with dedicated cores, each second of CPU time accomplishes roughly the ...


1

Check out this interesting post from David Klee: VM Memory Counters Lie for SQL Server VMs In particular, this quote is very relevant to your situation: SQL Server uses memory as a working set I/O buffer, and memory blocks in the SQL Server buffer pool are most commonly used as a read cache. Memory blocks that are repeatedly read only show up in the ...


2

I don't think there's a way to do that in SSMS. You should use Sentry One Plan Explorer for this. It's a free tool that provides an alternative way of viewing SQL Server execution plans. I've run this code on my local copy of the Stack Overflow 2010 sample database: CREATE PROCEDURE #sp_TestQuery AS SELECT COUNT_BIG(*) FROM dbo.Users u WHERE u.Id > 10;...


1

Sounds like DOMAIN\GroupName is a group and DOMAIN\user.name is a member of that group. This means that DOMAIN\user.name has access to SQL Server through that group. You have 2 options: Create another group, let's say DOMAIN\NewGroupName and grant that group INSERT and UPDATE, then make DOMAIN\user.name a member of that group. This is not the fastest ...


0

I tried different scenarios. Number one is for me create new catalog and do re-deploy from VS. If someone know how can we do restore db, please write here.


3

Like @Shanky mentioned in the comment, it could be due to Express Edition accidently being installed instead of Standard or Enterprise. Due to the Database size size limit, I don't think you could even restore a large database, so you would notice almost immediately. As an experiement, have you tried caching a lot of data in memory by doing several large ...


3

The only idea I can think of is if I maintained a copy of the database on a separate server where I can make DDL changes, then re-point my applications to that server. Instead, consider just building a new table, and loading it incrementally from your existing table (things like Change Tracking or even Triggers can help here). Then during your short ...


4

The best thing I have found to help in big index creation is to (1) have enough RAM, (2) have a slow time before the index creation starts, and (3) perform a SELECT...INTO of the index fields into a temp table with an ORDER BY of the desired index order right before creating the index. This can speed the process by up to 75% in some cases (which I have not ...


0

Talked to some colleagues. One really ran the Tuning Advisor without knowing that this caused the problem


0

Why am I getting this error in this scenario? The agent itself is switching QUOTED_IDENTIFIER to OFF. I have written about it here (mind the unreadable font, not my choice). Here are the tests run on sql server 2012 to sql server 2017: If I run this query in a ssms query window: DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF'; IF ( (256 & @@OPTIONS) ...


3

You need to remove the log shipping metadata. The correct way to do this is via stored procedures, which also removes the jobs (I'm guessing you've deleted those manually). Execute sp_delete_log_shipping_secondary_primary. This removes the information about the old primaries on the secondary server (Server A). Also removes the jobs if they exist. Execute ...


2

It would be at least a little faster, and also use less disk space, depending on how many rows there are with UniverseName = 'Unknown'. The original index would allow the query to seek to the correct movie title starting character, and apply a residual predicate to each row to make sure it's not from an 'Unknown' universe. The filtered index would still ...


0

From your plans I see the problem in this statement: INSERT INTO #Analysis_ClientAccess_SurveyInstances_SurveyInstances ([SurveyInstanceID]) select SI.[SurveyInstanceID] from [dbo].[SurveyInstances] SI inner JOIN #Analysis_ClientAccess_SurveyInstances_ProtoSurveysFiltered PSF on SI.[ProtoSurveyID] = PSF.[ProtoSurveyID] inner JOIN #...


0

I managed to find the solution in the first answer to the question here: https://stackoverflow.com/questions/29676432/alphanumeric-sort


2

You need to parse the string to separate the alpha + numeric part. Assuming that it is always alpha first follow by numeric and not the other way round the 2 cross apply split the alpha and numeric part from the string select * from yourtable t cross apply ( select alpha = LEFT(your_col, PATINDEX('%[^0-9]%', your_col)) ...


0

Correct, once the Primary loses connectivity with the Secondary (closes), all in-flight transactions will be rolled-back on the Primary and never make it to the secondary. The committed transactions are guaranteed to be written to disk on the mirror server in that mode. Here is an excellent article with more detail on this subject - https://www.sqlskills....


4

Perform the following commands in SQL Server Management Studio (SSMS) on the SQL Server instance you are trying to restore the database to. Verify BAK File RESTORE VERIFYONLY FROM DISK = 'T:\DIRECTORY\DATAWAREHOUSE_FULL_20200115_190000.bak' This should provide you with a result set that looks like this: The backup set on file 1 is valid. Possible Errors ...


13

There are really two questions in this question: Why is the query slow? Why are tempdb properties inaccessible? Let's start with #2. Why are tempdb properties inaccessible? You get this error: Cannot show requested dialog. (SqlMgmt) Property SpaceAvailable is not available for Database '[tempdb]'. This property may not exist for this object, or ...


0

Upgrading your two 2017 instances to Enterprise is a simple process. SQL Server Setup has a function called Edition Upgrade, which is a fairly straightforward process. Your SQL 2016 Standard to SQL 2017 Enterprise is more complicated. You need to assess your environment and determine what best suits your resources, skills and appetite for risk. Microsoft ...


1

To maintain an unbroken ownership chain among databases with views, the databases must be owned by the same login in the case of dbo-owned objects. Also, the DB_CHAINING option needs to be enabled for the databases involved. The user needs connect permissions to all databases accessed by the view but only permissions on the view need to be granted. Be aware ...


4

This is down to the implementation of how VARCHAR(MAX) and NVARCHAR(MAX) actually store their data. If the number of bytes needing to be stored per column is less than or equal to 8000 bytes then the data will be stored in-row, if it is greater than 8000 bytes then it will be stored in LOB pages. When this happens, the the row holds a pointer to the LOB page ...


2

Check out the Query written by Pawan: http://pawansingh1431.blogspot.com/2011/02/check-what-are-sql-components-installed.html


4

You can run "Installed SQL Server features discovery report" It is documented there: Validate a SQL Server Installation Some examples: how-to-find-out-what-sql-features-are-installed


6

Multiple parallel threads for the same query all share the same session_id, or spid. Kendra Little did a great write-up showing that here. Brent Ozar Erik Darling also has a great, if only tangentially related, post about it here If your query is blocking itself, that indicates one thread is taking longer to complete its work; this might mean you have out-...


0

First, we need to create a working dataset to pre-summarize some values: WITH Dataset as ( SELECT sc.empID as empID, MAX(sc.ars) as ars, -- Avoid extra GROUP BY SUM(ISNULL(ic.lit,0)) as SumLit -- EDITED - fixed incorrect code copy. FROM salesconfig sc LEFT JOIN income ic ON sc.empID = ic.empID GROUP BY sc.empID ...


1

So, you are looking for the difference in Years between the two dates, correct? And you want to show one decimal place in the calculation, correct? Then a simple DATEDIFF() will not work for you. Try this instead: SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1) This is also imprecise (because of leap years) but matches ...


0

Test Select case when sc.ars = 0 then 0 else SUM(sc.ars / NULLIF(SUM(ic.lit) OVER (PARTITION BY NULL),0)) end FROM salesconfig sc Left Join income ic On sc.empID = ic.empID


0

Here's the final script, using the script from Eduardo Pivaral, it loops through every table in order of foreign key dependencies, finds its primary key and builds a merge statement to update or insert the rows. declare @table varchar(255), @stgtable varchar(255), @updatecolumns varchar(max), @insertcolumns varchar(max), @valuescolumns varchar(max), @pk ...


4

There are several ways to get this that require different input information INFORMATION_SCHEMA: You only need to know the name of the object you want the definition for. SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'MyProcName' Note, this schema is database specific, so you need to be in the context of ...


1

You can also group it in inner query. SELECT emp.ID, emp.fname, emp.lname, emp.address1 , emp.address2, emp.city, emp.state, emp.zip, emp.phone, s.Sales FROM ( SELECT emp_id, SUM(sales) AS Sales FROM empPerformance WHERE saledate BETWEEN @startdate AND @enddate GROUP BY emp_id ) s JOIN employeeData emp ON emp.Id = s.emp_id


1

You may be running into a scenario where the (perhaps default) sampling percentage isn't sufficient for the stats to be useful for longer periods of time. You can quickly check to see what sample rates were used during the last UPDATE STATISTICS statement via the following query: SELECT OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) AS ...


2

There is multiple ways to fix the parameter sniffing issue. If you update your statistics 10 times a day and especially if that is causing performance issues, then I would look at other options. There is some good chance that the update of the stats is not what's fixing your parameter sniffing issue. The update of the stats causes the plan to be ...


0

I know this is an old post, but just in case somebody searches for this issue.... Check if a DBCC check is running at the same time. If so, then I'd suspect that the DBCC snapshot files are not excluded from a virus scan. Our CISCO AMP scanning the alternate data stream prevented database connections and generated non-yielding scheduler errors. Excluding ...


2

You could use dynamic sql to check the existence of databases in linked servers when using parameters. An example: DECLARE @SourceHost nvarchar(50) = '[CGENMONOSAMP02]'; DECLARE @SourceDB nvarchar(50) = 'Sample_DB'; --@SourceHost is CGENMONOSAMP02 --@SourceDB is Sample_DB CREATE TABLE #temp ([name] nvarchar(50)); IF NOT EXISTS (SELECT [name] FROM master....


2

You could use OPENQUERY() to be able to query the linked server and apply FORCESEEK / FORCESCAN hints. Example: SELECT * FROM OPENQUERY( [Server1], 'SELECT DISTINCT Table3.Field5 FROM Server1.Database1.Table1 AS T1 INNER JOIN Server1.Database1.Table2 AS T2 WITH (FORCESEEK) -- Index exists for T2.Field2 ON T1.Field1 = T2.Field2 INNER JOIN Server1....


3

You CANNOT use FROCESCAN or FORCESEEK on remote data sources. Reference: Hints (Transact-SQL) - Table The FORCESCAN hint has the following restrictions: The hint cannot be specified for a remote data source. When using the FORCESEEK hint (with or without index parameters), consider the following guidelines: The hint cannot be specified for a remote ...


0

it depends on the failure mechanism you prefer. If, when a new column is added, it can always be safely ignored (eg: select firstname,lastname from customers where id=@x), then never use select * - the more columns you touch, the less chance that a helpful index can be found/created. If when a new column is added, silent discarding of it would be bad (any ...


6

One problem is that you could have circular references. You may prefer to disable the constraints, do your ETL, and reenable them. This could also be a matter of timing, such that it may be impossible to populate some data because of when you got the data from the source. For example, you get data from table P and then from table C. But by the time you’re ...


0

Does SQL Server MDS support creating a Model by reverse engineering a database? I wrote a command-line utility to do this, called MdsModelImport. It's intended to be a one-time import, and simply save you from having to type all the attributes.


6

For SQL 2016 here is the list why 'statement_recompile_cause' can happen. Schema changed Statistics changed Deferred compile Set option change Temp table changed Remote rowset changed For browse permissions changed Query notification environment changed PartitionView changed Cursor options changed Option (recompile) requested Parameterized plan flushed ...


2

And, there several different developers which developes that CRM web app. They know the user name and password from the web config file they develope. Some of them can connect to my database with that user and execute some DDL and DML commands. And i want to block them by their client hostname. If a developer connects to db from his/her machine ...


Top 50 recent answers are included