New answers tagged

3

Column statistics are created automatically as needed by SQL Server when the AUTO_CREATE_STATISTICS database option is ON and also by an explict CREATE STATISTICS statement. With the AUTO_CREATE_STATISTICS database option, the query optimizer will create single column stats during query compilation as needed on columns specified in query predicates when a ...


0

It's not possible to achieve without using temp table as the DMVs/DMFs that we are using are database scoped and requirement is server scoped, and i don't see any difficulties or complication using temp table as follows: Declare @Tbl table (ServerName varchar(128), DBName varchar(128), SchemaName varchar(128), TableName varchar (100), IndexName varchar (100)...


0

As pointed out by @SQLRaptor in a comment, you're running this code against an empty table. In fact, it's a table variable, which can only contain rows while the code is running. The following example shows that your code works if rows are inserted into the table: DECLARE @Table TABLE (Col1 int, Col2 datetime); INSERT INTO @Table (Col1, Col2) VALUES (1, '...


6

I would suggest to use Extended Events instead of Profiler as the Extended Events has less overhead and more events to capture compare to Profiler. In your case, create a new session in Extended Events by selecting rpc_completed event. For detailed steps..


0

In your Trace Properties, I would recommend using the Tuning template. Under the Event Selections tab in the Trace Properties, choose the RPC:Completed event under Stored Procedures. Within the event filters tab, select Show All Columns. Using the Column Filters: DatabaseID Equals the DB_ID() of the database where the stored procedure exists. ...


1

Given these queries, is it useful to create a separate (non-clustered) index on just the BillID column? First, note that an index on BillId will include TaxCode. The clustered index key is the row loacator for this table, so every index must contain it. And for non-unique indexes, the clustered index key columns are added as index key columns to make the ...


1

Does implementing gMSA works for 2 node SQL FCI and 3 or 4 Node Always on AG,s? Yes it does work. Since I just want to run SQl server with that gMSA account is there any special permissions needs to be granted at server or database level while running both SQL and agent services? Nope, the installer will take care of that. Please advise any catch of ...


1

Sometimes you need to open firewall rule for UDP 1434 SQL Browser port. Be sure SQL 2012 and 2016 don't use same TCP port (1433). If multiple instance on same machine they should be configured to use separate TCP port.


0

Yes it is true that you should not change an actively running stored procedure, because SQL Server doesn't like it. As for taking downtime, well yeah, you shouldn't be making changes to frequently-used stored procedures in a busy production environment during operating hours as a best-practice.


1

I don't think it is possible using a Sequence. Sequence use cycles between numbers like bigint, and cycle to either the min or max depending if its an ascending or descending sequence. This problem can be solved by using a Trigger on Insert which performs an update to the field you aim to have a cyclic sequence number based on ANO(Year). First create the ...


1

I found last_wait_type is ASYNC_IO_COMPLETION for this backup job. We have sufficient disk space available. This blogpost from Paul Randal gives you most of the information regarding this wait stat ...The official definition of ASYNC_IO_COMPLETION is ‘Occurs when a task is waiting for I/Os to finish.... ...Long-duration ASYNC_IO_COMPLETION waits ...


2

Here I may be placing an emphasis on your words which you did not intend but I think it is a point worth making. You say "I would employ CHECK constraints to ensure that data was supplied for sets of fields." You cannot. No setting within the DBMS can affect what comes in from an up-stream system. In-coming data is what it is; complete or not, correct or not....


2

Have a look at the wait_stats and it will show what the biggest bottlenecks are on your SQL server. I recently experienced a problem where an external application was intermittently slow. Running stored procedures on the server itself was always fast though. Performance monitoring showed nothing to be concerned about at all with SQL Caches or the RAM usage ...


3

I wouldn't be too concerned about 25 columns. That's not an extremely high number. It doesn't even approach violating Swart's Ten Percent Rule (102 columns). Of course, the "rule" there is discussing a limit, not a goal, so keep that in mind 😃 In my mind, ideally, each of these separate "forms" (tabs) would be backed by its own table (AlcoholAbuse, ...


0

My advise is to first identify the workload and purpose of the table (read and write): 1.(Write > Read): If write activity is more than read activity on the table then it's better to have normalized tables, that means we minimize the purpose of a table i.e. instead of having Sales, Customer and Products data in 1 table, better maintain separate tables per ...


2

Do you think that this will be improve performance on the table . No. You'll probably just end up JOINing the smaller tables together.


12

In SQL Server all the sequence numbers are generated on a single instance so they are already inherently ordered. And if you’re just using them as unique numbers it shouldn’t really matter anyway. The Oracle implementation is different because in a RAC environment (not all Oracle implementations), two sequence numbers could be generated by two different “...


1

I discovered that the named instance does not use default SQL port, but uses port 8000 (which is open on the firewalls). I tried specifying the port number in the connection string like this: Yes, but the named instance ports are dynamic and managed internally by SQL browser service which use the port number 1434. In your case port# 8000 should not be used ...


2

Ok, so after few trial and error queries I managed to solve this. It may not be the best way but it seems to work fine. select * from ( select CAST([datetime_received] as date) AS ForDate, DATEPART(hour,[datetime_received]) AS OnHour, COUNT(*) AS Totals from [claims] where [datetime_received] between '2019-07-01 00:00:00.000' and '2019-07-31 ...


2

Possible realization. Fulltext search replaced with common LIKE substring search. Source table: CREATE TABLE test (val VARCHAR(16)); INSERT INTO test VALUES ('1 chaining'), ('2 fullfill'), ('3 full chain'), ('4 no pattern'); Patterns table (can be temporary or table-type variable): CREATE TABLE patterns (pattern VARCHAR(8)); INSERT INTO patterns VALUES ...


0

mcnets: Can't you use: SELECT CASE WHEN CONTAINS(Name, '"chain*") THEN 'chain' ELSE 'full' END akina: CASE WHEN CONTAINS(Name, '"chain*") AND NOT CONTAINS(Name, '"full*") THEN '"chain"' WHEN CONTAINS(Name, '"full*") AND NOT CONTAINS(Name, '"chain*") THEN '"full"' ELSE 'both chain and full' END looks more informative.


0

You'll need to indicate that you want to use the named pipes provider in the connection string. Consider this VB.Net code that connects to my local SQL Server 2012 instance via Named Pipes: Module Module1 Sub Main() Dim cb As New SqlClient.SqlConnectionStringBuilder With { .DataSource = "\\.\pipe\MSSQL$MV2012\sql\query", ...


0

Here you go, is this what you meant? This should generate a dynamic SQL command for you with all the tables in "JoinTables" (I used McNets's answer as basis): /* -- initialization: IF OBJECT_ID('JoinTables') IS NOT NULL DROP TABLE JoinTables; CREATE TABLE JoinTables ( TableName SYSNAME ); INSERT INTO JoinTables VALUES ('Table1'),('Table2'),('Table3') */ ...


0

First of all, your requirement is best suited for Instead of Trigger. Trigger is best suited for your Requirement than Function. Sample data of joinTable, create table #oinTables(obID int,firsttable varchar(20),secondtable varchar(20)) insert into #oinTables values (1 , 'tbl1' , 'tbl2') , (2 , 'tbl1' , 'tbl3') ,(3 , 'tbl1' , 'tbl4') ,(4 ,...


1

I think you could get it with a single statement. CREATE TRIGGER t1_insert ON T1 AFTER INSERT AS BEGIN IF EXISTS(SELECT 1 FROM inserted JOIN ( SELECT ID FROM T2 UNION ALL SELECT ID FROM T3 UNION ...


0

Seems like an open transaction (waiting to commit or rollback) could cause the secondary replica to stop answering queries. To find such a transaction on the primary, another alternative could be: DBCC Opentran


0

Since you said that , ,INSERTs are relatively rare (at most dozens of times per day, but typically much less than that), reads are very frequent (essentially on every web request to my application).INSERT performance doesn't matter too much, but I'd like to avoid horrible index fragmentation if at all possible Alter your Table to add RowNumber ...


0

Workaround: The accepted answer seems to indicate that the conversion needs to happen locally because the OLEDB driver doesn't support it. So I think a simple workaround (at least in the case of my query which is selecting a null uniqueidentifier in the base case of a recursive CTE) is to declare a null variable: declare @nullGuid as uniqueidentifier = ...


1

This should do the work. I tested it in my test environment, works for me. USE [master] GO CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [TutorialDB] GO CREATE USER [TestLogin] FOR LOGIN [TestLogin] GO GRANT CREATE TABLE TO [TestLogin]; GO Create a Login on the server with public ...


0

Firstly, if they are installing their application then surely it would require their own database and not access to the other databases on this server? However, granting their login member of the dbcreator server role allows them to CREATE new databases on the server. If you need to allow them to create a table in an existing database then I'd suggest that ...


3

I don't know if this is the right place to ask a question about Microsoft CRM but, is it safe to drop those indexes with 0 seeks, lookups and scans with tons of updates and rows? That will depend on multiple factors only you will know. (Both SQL Server & Microsoft Dynamics CRM related) SQL Server When was the server last restarted? Make sure ...


12

You could use Extended Events to capture this data. However, depending on how much traffic your server gets this could become a lot of data very quickly and could result in performance issues. I would look at limiting the events that you track to something like sqlserver.rpc_completed or sqlserver.sql_statement_completed, these only capture what was ...


0

Extended events seems like the best bet from what i've seen: https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-2017 Logging queries and other T-SQL


7

Use SQL Server Extended Events to capture T-SQL statements as they execute. SQL Server Management Studio 17+ includes an "XEvent Profiler" item in the Object Explorer for every connected SQL Server that is Version 2012 or higher. Right-Click the TSQL session, then Launch Session. Be aware, capturing T-SQL statements across an entire server can ...


2

Clustered Index : Help to store table data on on pages in sorted order according to key column. So you can say it's sorted table (clustered table). Non-Clustered Index : Store key value on IAM (Index Allocation Map) pages along with row address, like page id and row offset, that helps query to find rows on data pages. So you can say, after creating ...


2

So with the updated index the First Update works fine,it uses the updated Non Clustered Index , but for second update it ask me to create a non clustered index on column3. The updates will always work fine. Is this an update query you run very frequently? Because in general (extra) non clustered indexes slow down inserts. All your data is in the Clustered ...


0

I did not analyze your query but the fastest way to get a single result set is to insert Union All In front of the second and third query. Rerun the query and you will get three rows in one result. All of the output columns have to be defined the same for Union All to work.


5

Tracing the query used by ssms One way to do this is by taking the profiler's 'tuning' template and then tracing your login This query pops up SELECT (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize], (SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS ...


0

How access works in SQL Server unless the database is a contained database. : Login-->user-->role So to provide access, you need a login created which will allow access to the instance and then map this login with the required database. This will create the user and permissions (role) can be granted. Microsoft Doc


Top 50 recent answers are included