I have multiple instances of SQL 2012 Std Edition on a 40 physical core server.What I have done is the use the Process - SQLServr -% Processor time Stat and divided by 16 ( the max number of Cores Std ed. can use) as a instance level measure. I also use processor object stats to show how busy the server is. How to represent the servers CPU utilization?
View 1 RepliesI am trying to create a table that would represent a workload for each shop. In order to do that I need to have WorkLoad table and ShopWorkLoad table which is actually just aggregation of WorkLoad.
WorkLoad contains a list of following items:
current orders that are in the process (one select statement)
scheduled orders (another select statement)
expected orders (third select statement) that come through a third-party system
All of this needs to be live. So, for example, as soon as order is added to Order table it should be included in WorkLoad if certain conditions are met. Same goes for scheduled orders (which come from another table). Expected orders will be loaded on a daily bases (based on historical data).
ShopWorkLoad table is aggregation of WorkLoad table.
Currently I did it this way:
Added after insert/update trigger on Order table: when order is created/updated, if it meets certain conditions, it should be inserted in WorkLoad, otherwise remove it from workload if it's in there and doesn't meet conditions
Added after insert/update trigger on Schedule table: when order is scheduled, if it meets certain conditions, it should be inserted in WorkLoad, otherwise remove it from workload if it's in there and doesn't meet conditions
Running daily job that populates WorkLoad table with expected orders based on historical values
Final step is to create an indexed view vShopWorkLoad
My biggest concern is usage of triggers which call pretty complex logic to determine whether item should be added to workload or not.
One other option was to create vWorkLoad view and somehow make it an indexed view but currently I don't see a way of doing that because the query consists of 4 union select statements, below is pseudo example. But even if doing it that way, how to build aggregated indexed view on top of vWorkLoad indexed view?
Third option is to use sql agent job which would run every x seconds (maybe 20) and it would execute all of these queries to populate WorkLoad table with delay of 10-20 seconds, but I am still not sure if this is acceptable to the client.
Fourth option is to create 3 or 4 indexed view where sum of them makes a workload. Then, ShopWorkLoad view would be built on top of these 3 or 4 indexed views, but in this case I don't know how this would affect performance since ShopWorkLoad query would be often queried.
Example of workload pseudo query:
select
WorkLoadType = 'Order in process',
OrderId,
ShopId,
...
from
Order
[Code] ....
How 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
We have two server configured in always on mode .
windows is sql server 2012 64 bit edition and sql server is 2012 64 bit edition.
RAM installed on both server is around 65 gb of which 49 gb is max server memory allocated for sql services on both servers.
database related to reporting services are also in always on group .
We have also configured for reporting services and both are running on their respecting server.
Issue is on primary server reporting service is using almost 7 gb while on secondary it is using 10 gb even when there are 5 reports and its used within offices .
what issue and how to check why ssrs is using high memory..
any query , perfmon counters
reports are randomly used at client side
i have checked memory utilization through task manger..
Hi All,
Bit of a funny one. I have two tables, lets call them PetOwner and Pets
PetOwner
OwnerID OwnerName
1 Tim
2 Paul
3 Andy
Pets
OwnerID PetName
1 Tricia
1 Louise
2 Bill
2 Ben
2 David
2 Topsy
3 Flopsy
3 Mopsy
And I need to write a query that returns a resultset like this
OwnerID OwnerName PetName1 PetName2 PetName3 PetName4 PetName5
1 Tim Tricia Louise NULL NULL NULL
2 Paul Bill Ben David Topsy Flopsy
3 Andy Flopsy Mopsy NULL NULL NULL
Can anyone help?
N.B. A pet owner can only have a maximum of 5 pets, but even if no-one in the database has 5 pets, the PetName5 column must still appear (but have a value of NULL).
This might not be possible, but on the chance that it can - is there away to do the following:Given a arbitray one dimesional value list:('AALGX','12345','XXXXX','AAINX','AMMXX')Is there a way that I could do a select statement, or similiar, in thevalue list, to get the following resultfield_name-----------AALGX12345XXXXXAAINXAMMXXBecause, what I want to be able to do in the long run is essentiallyperform an outer join on the value list.Something along the lines ofselect value_list.field_name, dbtable.otherfield FROM value_list leftouter join dbtable on value_list.field_name = dbtable.field_nameSo I want all the values in the field list to show up, and anymatching data in the database table that exists, otherwise null.Maybe there is another approach to this???Thanks!KT
View 4 Replies View RelatedI have 2 server PROD n DR server all the PROD server DB is synched to DR server through log shipping. Now to chk whether the DR server is working or not in case of any disaster we shut down the PROD server now all the app request is handled by DR server,it is working fine. For 1 week we work in DR server now its time to move back to PROD server so that all the 1 week data in DR server should be replicate back to PROD server. Log shipping is deleted once the DR Server is up. What r the options other than Backup? It is SQL 2012
View 4 Replies View RelatedThis is a tough error....
The situation: we are trying to manage Stored Procedures (ALTER, CREATE, DROP statements) on one of our SQL2005 servers and this error spews out:
"Target string size is too small to represent the XML instance"
Now this script i wrote (simple alter statement adding a column) works on 4 other versions of SQL 2005, but when i try a certain server, i get that error
Searching Google, Yahoo, and MSN, all three point to one single instance of this error, right here on Don Keily's blog
http://www.sqljunkies.com/WebLog/donkiely/archive/2005/10/20.aspx
and unfortunately... no solution :(
Maybe in the 3 months since that post, someone might have run across that and knows how to fix?
I have a simple table as shown:
I want to have values on the last column to represent the time interval between the 2 date columns (visits); i.e for event-ID 2 for example, I will have
entry(EventID = 2) - exit(EventID = 1), and so on
I would like to know if it is possible to split a database across different servers, in the same manner you can split it over multiple drives on the same server We are trying to balance the load cause we are finding that the current server can't handle the load
View 4 Replies View RelatedI want to compare the database structures(columns,datatypes..etc) across same databases located in different servers.
View 9 Replies View RelatedOne of my client asking mirroring between two server with witness.Is it possible witness and mirror in same server(with same instance)? OR Is it possible witness and primary in same server(with same instance) ? Best method to establishing mirroring with in two servers(with witness) ?
View 4 Replies View RelatedI am trying to reproduce a problem that is probably related to how a particular SQL server is configured. Is there a tool or best practice that is useful to compare all of the configuration settings between two SQL Servers (same version)?
View 2 Replies View RelatedI have installed SQL Server on Server A. I have installed Integration Services on Server B.I found that you need to addto the integrations services MsDtsSrvr.ini.xml file the server for the database if you want to use MSDB package store. The issue I have now is how do I execute the jobs on the Integration Server. I dont want them to execute on the SQL Server. Is there a way to configure the SQL Server Agent jobs to execute the package on the other server? Do you have to setup windows scheduled tasks or some other tool to execute command line packages on the IS server?
I'm looking for any info on segregating these roles but still using the MSDB store for pacakages. I don't want SQL server installed on the IS server or IS on the SQL Server. I want the Integration Services server to do all processing... not database level obviously but flat file reads, web service calls, data manipulaiton, custom code, etc and just pass the input/output of data to the SQL server for storage or retrieval to offload the workload and segregreate responsibilities.
Does MSDTC auto-install with the plain vanilla version of SQL Enterprise? Or do I have to install it later?Do you know of any links that reference specifically SQL 2012 stand-alone server installs of MSDTC?
View 0 Replies View RelatedI have to build a database model, create tables and use triggers or/and procedures on it. It's about a library. Books are borrowed by students. They can make a reservation of a book.
I built the database model so far. My problem is how to represent following tasks:
- Students can borrow a maximum of 5 books at the same time.
- bookings/book reservations should be later processed in the order they occurred. That is, who first flagged the book also gets the book/books first
I have following entities:
- Book
- Book exemplar
- Borrowed by (a table between the entities Book exemplar and Student)
- Reserved for (a table between the entities Book exemplar and Student)
- Student
How would you represent the tasks I mentioned above in terms of triggers or procedures?...
how I can set up HADR between two stand alone servers? Is it even possible to set up WSFC between these two servers?
View 3 Replies View RelatedScenerio : To keep a very large system running optimally in a VM cluster, Take PR01 and make PR02, PR03, PR04. Distribute the 45 databases and 9T+ of disk across multiple VM guest.
Each PR## is a SQL Server 2012 Enterprise guest on a VM 5.1 cluster.
So instead of PR01 needed 16 core and 128g of memory, each one will have 4 core and 32g of memory. Making VM HA more manageable. (yes, DRS rules will apply). Also provides more HBA paths and distributes i/o over more physical disk on the SAN.
Instead of a connection string having to know PR01.dbo.UserDB01, PR02.dbo.UserDB03, ect the connection would be PRDB.dbo.UserDB01. That way if needed 1) UserDB can be moved to any of the PR## 2) new PR05, PR06 can be added as needed. The end user and processes are not allowed to touch system databases, no PR## will have a user DB called the same name.
There are seperate VM guests on other VM clusters and Citrix servers that need access to PRDB. As things expand and move around, none of the connection strings need to be changed.
I am looking into RadWare and modifing level 7 information, but that is iffy and $$$$$$.
I've just restored a DB from one server to another. Part of the DB is an assembly used in a Function that unencrypts some data held in a Varbinary(Max) column.
When I execute it on the original server it works fine across all rows of the table, when I execute it against the new server it fails because it's seeing dates in MM/DD/YYYY format, it works if the day of the month is less than 13, but obviously the date would be wrong!
Setting the DATEFORMAT to MDY prior to executing the function has no effect either.
It's the same DLL in the same location, the SQL Server settings as returned by DBCC USEROPTIONS() are identical.
The SQL Server editions and Window OS are the same
New Server
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
Old Server
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
I'm thinking the DLL is being affected by some setting either in the Windows OS or a SQL Server setting ...
Server: SQL Server 2012 Enterprise 64bit
I created a maintenance plan with full user database backups, backup files cleanup, and database shrink.
I did it in SSMS and it also created "Reporting Task for subplan-{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}".
It works fine in the development server where I created the plan. I imported the same into other environments test, stage, and prod. I did use BIDS to change the connection string manual "Local server connection". The job runs fine in other environments but it fails at the end where the "Reporting Task for subplan" is executed. I looked at the dtsx file codes and I could not find anything wrong.
It seems to be a bug at the Microsoft side.
So I have 2 servers S1 & S2.
Database Group 1 = L1 with Primary S1 and Secondary S2
Database Group 2 = L2 with Primary S2 and Secondary S1
For 99% of the time the 2 groups of databases are not related. For the 1 procedure that does move data from L1 to L2 something like
Update L2.DB.Owner.Table
set flag = 1
Whare a = 0
On S1 I have a linkedServer with connection to L2.
If I have a failover I cannot have L2 on S2 as they are essentially the same server.
How to I use the 2 groups hand in hand.
Is it possible to use registered servers feature in clustered environment and with different versions of Sql server like 2012 & 2014.
View 0 Replies View RelatedCurious what the industry standard is combining multiple instances on single server ?
Right now i have separate servers for OLTP, SSAS, and SSIS, and for OTP have Development, Test, and production environments. Im considering combining the SSIS and SSAS services into additional instances on each environment servers.
Now (Production)
OLTP
SSAS
SSIS
After
1 Server with 3 separate instances for SSAS, SSIS, OLTP. Then replicate this model through the environments to utilize development and testing.
I need to automate the db users to get the data for every month on all servers ....
View 9 Replies View RelatedWhat happens when an automatic failover occurs, in a two server AlwaysOn Availability Group configuration, where the secondary replica is configured as read-only?
Will it only allow read-only connections, or will it become read-write and can accept INSERT, UPDATES and DELETES when assigned the new role as Primary?
Is it correct that adding a third server/node, that just acts as passive and should be used for automatic failover, to support true HADR, would NOT need another license .. and that licenses would only be required for the previous Primary and Secondary (Read-Only) replicas?
I have a HA Listener which is visible and can be connected to, it has a read only secondary on a different subnet so when connecting to it we use the applicationintent = readonly and multisubnetfailover = true.
Trying to connect it as a linked server is giving me problems. I tried putting the extra info into the provider string but keep getting the failure to initialise error. I am trying to link SQL2012 to a 2012 HA group but will also need to connect from a sql2008 server as well
I am trying to understand creating SQL Server projects and managed code. So I created a C# SQL Server Database project and named it "CSharpSqlServerProject1" and followed the steps in the following "How to: " from the Help files:
"How to: Create and Run a CLR SQL Server Stored Procedure "
I used the exact code in this "How to: " for creating a SQL Server managed code stored procedure (see below) in C#. However it didn't even compile! When I went to build the code I got the following error message:
"Error 1 Target string size is too small to represent the XML instance CSharpSqlServerProject1"
It does not give a line number or any further information! Since this is a Microsoft example I'm following I figure others must have run into this too. I can't figure out how to fix it!
Here's the code as copied directly from the howto:
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure()]
public static void InsertCurrency_CS(
SqlString currencyCode, SqlString name)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand InsertCurrencyCommand = new SqlCommand();
InsertCurrencyCommand.CommandText =
"insert Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
" values('" + currencyCode.ToString() +
"', '" + name.ToString() +
"', '" + System.DateTime.Now.ToString() + "')";
InsertCurrencyCommand.Connection = conn;
conn.Open();
InsertCurrencyCommand.ExecuteNonQuery();
conn.Close();
}
}
}
Thanks for any help you can give!
I know now that AlwaysOn feature HAS to be installed/configured on a Windows Clustering environment, BUT the secondary replicas, like the Disaster recovery replica residing in a different Data Center HAS to be also in a Windows Clustering environment or can it reside on a SINGLE SQL Server INSTANCE?.
View 6 Replies View RelatedI have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:
SELECT left(mf.Physical_Name,2) AS Storage_Drive,
DB_NAME(mf.database_id) AS DatabaseName,
db.create_Date,
DateDiff(day, db.create_date, getDate()) Age,
sum((mf.size*8))/1024 SizeMB
[Code] ...
How would I best accomplish this if I want to implement it using a TSQL procedure?
I am working on to create a data warehouse. I have made a database which will be the data warehouse and will consist of dimension and fact tables. I know that other than dimension and fact table a data warehouse should also consist of a meta data, now my question is what should be the structure of metadata and all the information it should have?
View 2 Replies View RelatedHi All,
We have two production boxes running different ERP Apps on each boxes, We just added 2 more cpu's to one box and 1 cpu to other. The Currect CPUS config as stands is
Box 1 = 4 CPU's
Box 2 = 2 CPU's
My boss whats some stats to see if performace was any better and if yes how much !!
How can i find such information from my box ?
Can anyonw help with this stat's ? !!
Thanks in advance
Girish
Hi all,
I have a problem in trying to find out why only one CPU in a 2 CPU H-T utilized. Using task manager, I can see 4 processors windows but only 1 actually utilized. I select +boost sql server and select all CPU for use. Queries and all other command ie dbcheck.. appear to use only single cpu.
Any help would be nice.
thanks
Andrew
Hi All,
I want to keep track of the CPU utilization & number of users connected for each database on our production box. I chose to get the data from sysprocesses table from master database.
But I realised that for some reason the master..sysprocesses.CPU column stays static or just keeps on adding to existing values.
Is there any ways thru which I can clear this data ( cpu column in sysprocesses table) after I have captured it in a table ?
Any help is appreciated.
Thanks.