I am running a query on SQL 2012 Server with the Resource governer setup for my account to have Max DOP option set to 1.
The query still runs in about 1 minute and the execution plan still considers parallelism.
When I explicitly mention the OPTION (MAXDOP 1) , the query runs in 6 seconds.
How can i tell by querying DMV's whether my query is using parallelism or not?
I was looking for a little insight on Resource Governor. I have a request from my manager to limit the resources a certain third party's DB can use on our system. We allow them to keep a DB on our SQL 2012 server so they can run query's to compare data to their server without linking the two machines.
I was looking into resource manager to perhaps accomplish this task but I'm not real familiar with it. If Resource governor can be used to only allow a DB named UserDB1 to use only 1GB or Memory at any one time?
Or perhaps there is another way aside from Resource governor?
I have got a question on max degree of parallelism and CPU cores.
If max degree of parallelism = 1, this signifies that sql will use serial execution plan (unless u change it in query level with MAXDOP hint). In serial plan, will the query use all CPU cores (say in my server I have 16 core processors)?
If in serial execution plan only one thread works, then what the other threads doing ? Idle (I may have a defined max server worker thread = 32767(by default)
Unable to create a relationship between this parameters.
hi,i've set 'max degree of parallelism' to 1 because some sql request hanged.Now when i connect, how can i set the parallelism to 4 for a session.Is there a command like this :'alter session set max degree of parallelism 4' ?ThanksPaul
View 6 Replies View RelatedI have SQL Server Version:-
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Â Jun 28 2012 08:36:30 Â Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Â
This is just an UAT server which has OS and hardware detail below:-
OS :- Windows Server 2008 R2 Standard
SP:- SP1
Processor :- Intel(R) Xeon(R) CPU Â X5650 @2.67GHz 2.66 GHz
RAM : - 4 GB
Bit - 64 bit
I want to set the value to max degree of parallelism, what value should i configure for the same?
Below is the snap property of SQL instance >> Processor
How to restrict resources usage based on individual Databases in resource governor?
We have many databases in one instance; I would like to restrict resource usage to each database respectively.
I created 2 pools as pool_login, pool_DBNAME, and 2 workload groups as GroupLogin,GroupDBNAME, and also the classifier function.After setup above, I use following statement to check what sessions are in each group .
Even if there are spids which are accessing database DBNAME, I can’t see that they fall into the group GroupDBNAME and pool pool_DBNAME.
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
ORDER BY g.name
GO
Following is the code to create pool, group,classifier function:
USE master
GO;
-- Create a resource pool pool_login.
CREATE RESOURCE POOL pool_login
WITH
[Code] ....
-- Create a workload group to use this pool.
CREATE WORKLOAD GROUP GroupLogin
USING pool_login;
GO
CREATE WORKLOAD GROUP GroupDBNAME
USING pool_DBNAME;
[code]....
-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
I want to create a classifier function where I can restrict all the logins except two logins for the resource pool.
Case Scenario is : I am creating a resource pool and on that pool I want to restrict the users for that particular pool but IÂ want to allow some of the user IDs full access to that databses so how could I create a script for that scenario.
I want to turn on the query governor cost limit option for 20 minutes so that queries do not run longer than 20 minutes but I could not find any info as if this option would also not allow database backup job or other maintenance jobs to run more than 20 minutes. We have backups (Via RedGate) run over 3.5 hours and others like rebuild indexes and integrity checks even more than 3.5 hours....
SQL Server 2012 SP2-CU1
Windows 2008 R2
So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
it is possible to create Linked server with Failover partner option. I can query when primary server and getting the error when I set the DB Fail over. I have tried with following script and also gone through different sources, but failed. Please see the script and error below.
EXEC master.dbo.sp_addlinkedserver
@server = N'MIRRORLink',
@srvproduct=N'',
@provider=N'SQLOLEDB',
@provstr=N'Server=primary;FailoverPartner=mirror;network=dbmssocn;',
[code].....
I have come up with an issue where I want to update data in a table using bulk/SET update to get the result shown in below code with output in column titled "Arrear Amt".
Please use this test data.
CREATE TABLE ##vOD_Calc
(
Seq_No INT ,
Contract_id INT ,
Rental_id INT ,
Actual_OD INT ,
Logic_OD INT ,
Due_dte DATETIME ,
[Code] .....
Logic required is that once the sum of column [ArrearAmt] of current row and all previous rows becomes greater than $100 then column [ChArrrearAmt] should show that summed up value and in else case the column [ChArrrearAmt] should show the same value as that of column [ArrearAmt].
Once the column [ChArrrearAmt] reaches the threshold of $100 then the same cycle should start again i.e. in above example rental#1 had $37.17 < $100 then rental#1 + rental#2 is also < $100 and at rental#3 sum of rental#1, rental#2 and rental#3 becomes $111.51 which is greater than $100 so its updated in column [CHArrrearAmt]. The same cycle start overs from rental#4 onwards however the summation of [ArrearAmt] will now begin after rental#4 onwards and not from the starting.
Below is the loop based SQL script which handles the above situation, however in BULK its a total deterioration of performance if thousands of rows are to be processed i.e. with a contract having multiple rentals.
The case here is that I have to use the result of previously updated column value of [ChArrrearAmt] to take decision for the next row, however with BULK update since the row is not yet updated with latest amount therefore the decision on next row is also giving wrong result.
This is the code with which I have achieved to update the column 'chArrear Amount', however its a loop based solution and performance killer.
INSERT INTO ##vOD_Calc_loop
( Rows_count ,
contract_id
)
SELECT COUNT(*) ,
T.Contract_id
FROM ##vOD_Calc T
GROUP BY T.Contract_id
[Code] ....
Hi,
I want to put a Degree sign in a SQL server field. Can anyone tell me how to do this please (preferably on the data entry screen rather than by coding it in, as I need it freqently).
Many thanks.
I have a ton of recompiles happening from a trigger:
INSERT INTO tableA (col1,col2)
SELECT col1, col1FROM INSERTED
OPTION(KEEPFIXED PLAN)
I added option keepfixed plan and it is not working, I am still getting recompiles in the profiler trace. The table with the trigger on it has a huge amount of inserts, updates and deletes all day long.
Does anyone have any ideas? I know triggers are expensive, but we want to keep this trigger (I don't have a choice to rewrite another way).
Thanks!!
We're using SQL2000 on Windows 2000 Server, but this is a problemwe've had on one particular database since SQL7 on NT4.The database in question is set to autogrow by 10% (currently sittingat 31Gb total size). However, last week users complained of aslowdown in performance. When we checked we found that only 14Mb wasfree on the database (we thought it would've grown automaticallybefore then), and when we added an additional 1Gb manually performancepicked up.Does SQLServer wait until all the space is used up (i.e. 0% free)before autogrowing? Even at that, we've never actually had thedatabase grow automatically - we've always had to add space manually.Settings on this database, and one that does grow automatically,appear to be the same (have also checked via sp_helpdb). So wheredoes the problem lie?Any help you can give would be greatly appreciated.
View 1 Replies View RelatedHow to get Instance wise CPU resource utilization report in sql server 2012? I have 2 sql server instance INST1 and INST2 running in window server 2012.I want report like this
Instance %CPU utilization
INST1 60
INST2 30
I am trying to push the install for ReportBuilder 3.0 and am having an issue with the REPORTSERVERURL option for installing via command line.I have a batch file that works fine, however when I launch the app it does not have a report server configured. I have verified I can connect to my report server if I enter it manually.
View 3 Replies View RelatedHas anyone had experience of using Parent/Child packages while enlisting them in Transactions.
I tested this on a small sample and thought that I had got it to work, but in my real-world package it does not.
The parent package essentially calls three child packages.
In each child package there are multiple DFT's that import and transform data into SQL Server.
All data must be imported or not at all.
Therefore I created a FELC container into which three Exec child package tasks were placed.
The FELC is set to Trans Option 'Required' and the Exec child package tasks to supported.
Unfortunately upon failure of one of the DFT's in the child the data was not rolled back.
So initially we had in terms of container hierarchy for the Trans Option property:
Parent package Supported
FELC for calling child packages Required
Task execute child package Supported
Child package Suppored
Tasks Suppored
Looking at this more closely we thought that we would need
Parent package Supported
FELC for calling child packages Required
Task execute child package Required
Child package Required
Tasks Suppored
for it to work.
However, the latter now gives us failures with error messages on the tasks on the child packages.
[Execute SQL Task] Error: Failed to acquire connection "Conn ECARS1CEDImport". Connection may not be configured correctly or you may not have the right permissions on this connection.
Even more strange the first couple of tasks in the child pkg complete successfully even though they use the same connection listed in the error.
These tasks also have Event handlers.
I am trying to change variable value at run time in ssis 2012 package using DTEXECUI utility but can not see any changes happening in config file variable value and also data is not getting populated in my table as per new variable value.
What is the right syntax or method of dynamically changing variable value either through DTEXECUI or DTEXEC command prompt command.
I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?
View 9 Replies View RelatedHi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
View 2 Replies View RelatedThis function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.
CREATE function [dbo].[fnc_myHinkley_ASSY_CalcWorkingMinutes] (@StartDate datetime, @EndDate datetime)
RETURNS decimal(14,2)
/*
Programmer: Goran Borojevic
Date: 5/14/2014
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM.
*/
AS
BEGIN
--check if one of the dates is null
if @StartDate is null or @EndDate is null
RETURN 0
[code]...
I need to be able to add minutes to a datetime value, which only cover working hours.
I have a holiday table as below:
Examples: (dd/MM/yyyy hh:mm)
Date..........Description
01/01/2015 New Years Day
26/12/2014 Boxing Day
25/12/2014 Christmas Day
25/08/2014 August Bank Holiday
Our Business hours are 08:00-18:00 Mon-Fri (unless the day is in the holiday table)
Start Date............Minute to Add......Expected outcome
01/09/2014 10:00........30...................01/09/2014 10:30
01/09/2014 17:00........65...................02/09/2014 08:05
29/08/2014 17:00........65...................01/09/2014 08:05
22/08/2014 17:00........65...................26/08/2014 08:05
31/08/2014 02:30........65...................01/08/2014 09:05
01/09/2014 19:00........65...................02/08/2014 09:05
01/09/2014 10:00........3005...............08/09/2014 10:05
22/08/2014 17:00........3005...............01/09/2014 17:05
I have tried to create a function to do this (fn_pp_AddMinutesWithinWorkingHours(@StartDate,@Minutes)) but I am unable to come up with a solution which factors in everything correctly.
I have SQL Server 2012, and I'm trying to execute a bcp with a queryout and it is not working with line break, I will send you an example of a simple query my real case is a very long query that contains line breaks but this example behaves the same:
DECLARE @StoredProcedure AS NVARCHAR(4000) ,
@sql AS NVARCHAR(4000)
SET @StoredProcedure = N'"SELECT *
from clarity.[dbo].[ACCESS_DEP]"'
[code]...
Of course that is not the solution as I have a very long query that requires line breaks.
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ....
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
Hi All,
I have written ETL software that runs on SQL Server. We are running it for the first time on a 4cpu (2 x dual core) machine on sql server 2005.
One of the things this software does is perform a 'select * from tablename' to validate that the tables passed to it as parameters exist. This has worked fine on previous releases and on single cpu machines because what the optimiser decides to do is to return just the first page of data and then fetch more. I guess it even works in 2005 standard edition.
However, 2005 enterprise edition allows parallelism. And what the optimiser is deciding to do with such a query is to parallelise it and fetch all rows and then give the result back to the program. So, instead of seeing a fraction of a second to return the first page of data we are seeing up to 90 seconds and the database goes and fetches 15M rows in parallel.
Obviously, what we would like to do is to somehow tell the optimiser that this set of programs should not perform any parallel queries. Or, we would like to turn parallelism off on the specific tables we are dealing with for the period of running these ETL programs....they have no need of parallel processing at the database level for virtually all the calls that are performed.
Would someone please be so kind as to advise us if we can do something like pass a parameter to ODBC to stop parallelism or if we can issue commands against specific tables to stop parallelism for a period and then turn it back on?
Thanks in Advance.
Peter Nolan
www.peternolan.com
Referencing an article regarding MAXDOP and cost threshold for parallelism from Brent Ozar's website: [URL] .....
We have a 2 physical CPUs that are 4 cores each with hyper threading enabled. When looking through the task manager, under the performance tab, I see 16 CPU threads.We have set the MAXDOP value is set at 4.
Reading further, cost threshold for parallelism setting is recommended at 50 to start with.
Our setting is at the default 5.
In always on under availability group server name properties can see the option Readable Secondary. In that for secondary server the Readable Secondary Option is YES and for Primary it is Read-Intent. I believe Read-Intent allows only read only connections and YES allows all user connections.
View 0 Replies View RelatedHi,
how can I efficiently find out the shortest connection in the following scenario:
there's a table which has two colums, both of the same type.
two items in the same row means that they are connected. (there is no direction of the connection so that a connection shows up in both ways in a table for query performance issues)
ie. item1 is connected with item2 and item2 is connected with item3, then the table looks like:
row1: item1 , item2
row2: item2 , item1
row3: item2 , item3
row4: item3 , item2
so the connection item1 to item3 would be item1 - item2 - item3.
degree of seperation is 2 in this case. how can I implement this in general with sql?
making an exhaustive search would cost too much time. thanks for any hints!
What the BUFFERCOUNT option does in a RESTORE DATABASE command?
View 5 Replies View RelatedI am trying to learn building SSAS tabular model. While following a tutorial I need to add a column to an existing table but for some reason the ADD Column option (insert column in other menu is also not appearing) is greyed out.
View 4 Replies View RelatedI have a customer who is looking to encrypt their applications connection to the back-end SQL server. I've gotten a valid certificate for the server from a trusted CA, and it's installed on the server (shows up in Personal certificates in the Certificate MMC when selecting the computer) and I can choose it in the drop down on the certificates tab of the SQL Network protocols properties in Configuration Manager.
Now, my question: If I select the certificate but do NOT enable the "Force Encryption" option, the client will be able to request encryption on their end, and the connection will then be encrypted. If the client (other applications) do NOT request encryption, they will still be able to connect, but their connection will be un-encrypted. URL....
Lastly, I could've swore I had found some information indicating that some methods of establishing a connection to SQL can fail when Force Encryption is enabled, as the method doesn't support SSL encryption.
Hi!
i make The Extended stored procedure for MS SQL Server which
Calculation of a degree of similarity of phrases.
Purpose:
One of the most complex and important problems for the developer and the operator of a database is maintenance of uniqueness names in the most important references of system.
Offered function can be used in SQL inquiry as criterion of sorting of the directory according to similarity with a required phrase.
Features:
· incredibly high speed of data processing
· Unique algorithm analyzing similarity of phrases even at significant divergences in required phrases
· is not required installation of additional libraries to each client - library DLL must benn installed only on a server.
· Result is all the table sorted in decreasing order phonetic similarity (probably use of operator TOP for sample only the limited quantity of the most similar variants)
· Use of user server function supposes use in Stored procedures, Views and any SQL expressions
· Spends a minimum of server memory
--------------------------------------------------------------------------
If the decision of the given problem is interesting to you and there is an opportunity desire and an opportunity to test http://kozin1.narod.ru (http://kozin1.narod.ru/newsite/index.html?english.htm)
Dll and sample scripts in rar archive (2,5 KB)
I thank in advance
In the Project Web Access (PWA 2010), I have created Report Library which contains .rdl files --> Manage Subscription --> Add Subscription --> Email subscription is missing
Did i missed out some thing but other email functionalities like notifications, Approval workflow are working fine so no issue with SMTP.