I thought, latching should be very short-term synchronization. From
systemprocess table, I saw the latch waited in a minute sleeping
without doing any work.
My database is about 23GB and more than 5000 tables. The RAID subsystem
is RAID1 with 1 disk mapped to C and D logically. Data files and tempdb
files are located in one location. Tranlog file and log backup files
are located in the same location with different disk spindle.
Currently, we are experiencing very slowness and IO bound. I'm ready to
rebuild the server by putting the RAID10 and 1 and distributing
multiple data files to different RAID10 and tempdb and log files to
RAID1.
Other than this, how to minimize the IO latch contention?
Do you have any stored procs or utility to track down the lead blocker as well as the blocked processes on SQL Server 2000? Similar to a tree structure with the lead blocker on top followed by the processes being blocked by the lead blocker.
I bet every SQL-Server guy in this forums has fought pageiolatch issues from one time to another. Either bad indexing, overloaded disks / controllers etc. I know I have, with a great variety of systems and solutions.
Anyways.... I simply have to ask for any of your experiences, as I'm currently stuck with a Navision client, that ocassionally stalls on pageiolatch on even simple queries.
User selects a customer account, asks to see his / her details, scrolls up and down a bit, and whammo, stall, and pageiolatch appears on the server.
Something tells me that this might be an ODBC driver issue rather than actual sql-server issues. Fetching the initial data isn't the issue, but scroling back and forth in the recorsset it seems to cause the cursor to freeze or at least lose track or stall in the progress of paging through the records.
All of the above sounds very unclear, I know, I'm just trying tro track down or center on the problem, so that I might find a solution. So if you have any experiences with clients, cursors and odbc driver version for sql-server, that really make a lot of trouble, let me know.
We're having problems with our SQL 2000 SP3 standard server (on Win 2k3). Our quasi-data-warehouse application does data processing on feeds of approximately 7 million records. Once the data is loaded in the queries against that data and updates against large tables will often wait with PAGEIOLATCH_SH contention. To give an example, over 7 million rows I was testing something out in development and issued: UPDATE <Table> SET <VarcharColumn> = null, which took forever (over an hour) with the PAGEIOLATCH problem....meanwhile someone else using another database was completely blocked from making an update during that time. It seems like something is very wrong.
The server has 4 drives spinning at 15k rpm with some sort of a high end raid controller, so I'm sure it's not a slow i/o subsystem.
Has anyone experienced this behavior before? Is this an issue that's resolved in 2000 SP4?
App developers are complaining that a blocking report I sent them today does not have enough info to troubleshoot and fix the root cause of the blocking.(I used SQL Sentry to get the report and send to the users, but I think my question is more fundamental than just using one 3rd-party tool.)The reason for the complaints is that the top blocker is a SELECT stmt on TableA that is not the Wait Resource of the blocking (TableB).
I explained that this is likely because the "blocking" SELECT stmt on TableA is part of a larger explicit tran that includes TableB, and that they need to look through their code to identify the SQL stmt that is the root cause of the blocking.They would like that root statement identified in the blocking report, but I don't think it is possible to get to that through DMVs.
Starting with a SQL Server 2000 database, my company built a new server with SQL Server 2005 and restored the SS2000 database from backups. We're now seeing some extreme issues where queries that took 30 seconds now take 3 minutes. Would this make you suspect index issues? Any recommendations on diagnosing and fixing this would be appreciated.
-- Business Rule, first name, middle name and last name can all be null-- ddlcreate table #cat (catID char(8) primary key, first_name varchar(15)null, middle_name varchar(2) null, last_name varchar(15) null)-- dml, populate sample datainsert into #catvalues ('Black123','ghost','','bigger')insert into #catvalues ('Arab0123','Hama','','Abbas')insert into #catvalues ('Mixed001','',null,null)insert into #catvalues ('Mixed002',null,null,null)insert into #catvalues ('Mixed003',null,'','Smith')insert into #catvalues ('White123','','','Talley')insert into #catvalues ('Yello123','Nick','H','Pisa')-- dml, name concatenation, get all or anyselect (first_name + ' ' + middle_name + ' ' + last_name) as namefrom #cat-- the above does not meet with requirement-- option 1select (IsNull(first_name,'') + ' ' + Case Len(middle_name) when 0 then'' else IsNull((middle_name + ' '),'') end + IsNull(last_name,'')) asnamefrom #cat-- option 2select (IsNull(first_name,'') + ' ' +IsNull(NullIf(Coalesce((middle_name + ' '),''),''),'') +IsNull(last_name,'')) as namefrom #catq:both option 1 and option 2 produces same result, which one is moredesirable?TIA.
DECLARE @MaxCountHistogram TABLE ( MaxId INT IDENTITY PRIMARY KEY NOT NULL, PublicationId INT NOT NULL, ProviderId INT NOT NULL, DateLog DATETIME NOT NULL, Amount FLOAT NOT NULL ) INSERT INTO @MaxCountHistogram VALUES(432,3,'20150530',10.2564),(432,3,'20150630',13.2564),(432,5,'20150530',8),(432,5,'20150630',13),(433,3,'20150530',9),(433,3,'20150630',11),(433,5,'20150530',13),(433,5,'20150630',21)
I need to take for each Publication and Provider and getting the diferential between two different months, for example:
Period Delta Amount Provider PublicationId 20150530 10.2564 3 432 20150630 3 Result of (13.2564- 10.2564 ) 3 432
I am using Northwind database to Create a view showing every order that was shipped to Spain. Name the destination column 'DestinationSpain'. Include code that checks if the view already exists. If it does, it should be dropped and re-created.
Here is my script:
use Northwind GO
/*STEP 2, #1*/
/* does it exist, if so drop it */ if exist (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrdersToSpain]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[OrdersToSpain] GO
/* Create the View */ create view "OrdersToSpain" AS SELECT Orders.OrderID AS Order_ID, Orders.CustomerID AS Customer_ID, Orders.OrderDate AS Ordered_Date. Orders.ShippedDate AS Shipped_Date, Orders.ShipCountry AS DestinationSpain FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.ShipCounty LIKE '%SPAIN%' GO
Here are the errors I am getting:
Server: Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'select'. Server: Msg 170, Level 15, State 1, Line 6 Line 6: Incorrect syntax near ')'. Server: Msg 170, Level 15, State 1, Procedure OrdersToSpain, Line 7 Line 7: Incorrect syntax near '.'.
I'm a newbe in the realm of database reporting. At my current position, I'm reporting off of CRM databases using Crystal V-11. Previously I'd experience working with HR databases using the same reporting tool. I am interested in progressing to work with database design and scripting. Any suggestion from anyone on which certifications to pursue?
We have accounts that pay for a particular "premium" service. It's entirely possible an account paid for this service for three consecutive months in 2013, then stopped paying, then started paying again. Why I'm trying to establish is, for the FIRST period of time the accout paid for this service, for how many consecutive months did they pay? Here is my test data:
if object_id('tempdb..#SampleData') is not null drop table #SampleData go if object_id('tempdb..#DateAnalysis') is not null drop table #DateAnalysis go
-- Use lead function to determine how many months are between
-- consecutive dates per account
; with DateInterval as (select AccountID, RandomDate, NextDate = lead (RandomDate, 1, NULL) over (partition by AccountID order by RandomDate) from #SampleData) insert into #DateAnalysis select AccountID, RandomDate, NextDate, datediff(mm, RandomDate, NextDate) as 'Lead' from DateInterval
where NextDate is not null -- Last row will contain NULL for NextDate. Don't include these rows.
-- Show the results
select *, 'NTile' = NTILE(3) over (partition by AccountID order by RandomDate), 'RowNum' = row_number() over (partition by AccountID order by RandomDate) from #DateAnalysis
Results (this is not getting me what I'm looking for):
The problem comes with accounts like AccountID = 1. They paid consecutively to start, then skipped, then started paying consecutively again. When using window functions, I'm running into trouble attempting to partition by AccountID and LeadInMonths. It's putting all the LeadInMonths = 1 together and that will give me skewed results if I want to know the earliest and latest date within the FIRST consecutive range of dates where the account paid. I've tried NTILE but it expects an integer and there's no telling how many "tiles" would be in AccountID partition.
I've looked at the OVER clause and the new "ROWS BETWEEN" syntax and still cannot get the desired results.
I have 12 month report and I need show volume and difference between current and prev month volume, what is the smart way to do this, do I need to put prev month value onto same row horizontally? I think should be some other smart way, I heard about LEAD function?
This what I think for now, It should be listed per ClientID also, in my example I have single ClientID for simplicity.
I tried to do LEAD but with not success..
/* IF OBJECT_ID('tempdb..#t') is not null drop table #T; WITH R(N) AS (SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N <= 12 ) SELECT N as Rn, 10001 ClientID, DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())) AS [Month],
