sql native driver 64 bit

Last Post 22 Jul 2008 01:36 PM by TRACEYSQL. 17 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
TRACEYSQL
New Member
New Member

--
16 Jul 2008 04:45 PM
We have some time outs on our SQL 2005 running 64 bit

Our 32 bit works just fine when you run this on a copy of the database.


SQL NATIVE Driver - any one have any ideas.

We check firewall opened up all ports etc.

Is there any time to live on these drivers ?
SQLUSA
New Member
New Member

--
17 Jul 2008 02:08 AM
Can you give us details?

64-bit SQL Server 2005 has some issues. Not exactly identical to 32-bit. Even some feature (not bits/bytes) behaviour is different.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
TRACEYSQL
New Member
New Member

--
17 Jul 2008 05:24 AM
We run a process in 32 Bit it does stop at a particular insert but then fires and continues. In 64 bit it just sits then does time out with message on the insert with ERR NO 17 DBNETLIB CONNECTION OPEN CONNECT SQL DOES NOT EXIST.

The only thing i can trace iin profiler and we have verified the statements and we verified the data in profiler is correct.

Anything else i can trace on the Profiler?

The database is set to be on CHECKSUM.

Not sure how to do trace with the driver could with ODBC.

This is not the only program that fails there are a couple of others which are more than a minute or so in processing.

We run at early morning with no activity ................still same error. Vendor has checked setup of our sql and the database.
TRACEYSQL
New Member
New Member

--
17 Jul 2008 09:03 AM
No blocking nothing.
Found this does this mean to put something in a connection string not on the sql itself under tCP/IP

http://support.microsoft.com/kb/888228

Update statistics all in place yes.

Ran this just before error
DBCC SQLPERF([sys.dm_os_wait_stats],clear) WITH no_infomsgs
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], GETDATE()
FROM sys.dm_os_wait_stats


MISCELLANEOUS 0 0 0 0
LCK_M_SCH_S 0 0 0 0
LCK_M_SCH_M 0 0 0 0
LCK_M_S 0 0 0 0
LCK_M_U 0 0 0 0
LCK_M_X 0 0 0 0
LCK_M_IS 0 0 0 0
LCK_M_IU 0 0 0 0
LCK_M_IX 0 0 0 0
LCK_M_SIU 0 0 0 0
LCK_M_SIX 0 0 0 0
LCK_M_UIX 0 0 0 0
LCK_M_BU 0 0 0 0
LCK_M_RS_S 0 0 0 0
LCK_M_RS_U 0 0 0 0
LCK_M_RIn_NL 0 0 0 0
LCK_M_RIn_S 0 0 0 0
LCK_M_RIn_U 0 0 0 0
LCK_M_RIn_X 0 0 0 0
LCK_M_RX_S 0 0 0 0
LCK_M_RX_U 0 0 0 0
LCK_M_RX_X 0 0 0 0
LATCH_NL 0 0 0 0
LATCH_KP 0 0 0 0
LATCH_SH 0 0 0 0
LATCH_UP 0 0 0 0
LATCH_EX 1148 203 15 62
LATCH_DT 0 0 0 0
PAGELATCH_NL 0 0 0 0
PAGELATCH_KP 0 0 0 0
PAGELATCH_SH 0 0 0 0
PAGELATCH_UP 0 0 0 0
PAGELATCH_EX 98 0 0 0
PAGELATCH_DT 0 0 0 0
PAGEIOLATCH_NL 0 0 0 0
PAGEIOLATCH_KP 0 0 0 0
PAGEIOLATCH_SH 4 15 15 0
PAGEIOLATCH_UP 0 0 0 0
PAGEIOLATCH_EX 2 0 0 0
PAGEIOLATCH_DT 0 0 0 0
TRAN_MARKLATCH_NL 0 0 0 0
TRAN_MARKLATCH_KP 0 0 0 0
TRAN_MARKLATCH_SH 0 0 0 0
TRAN_MARKLATCH_UP 0 0 0 0
TRAN_MARKLATCH_EX 0 0 0 0
TRAN_MARKLATCH_DT 0 0 0 0
LAZYWRITER_SLEEP 29 28000 1000 0
IO_COMPLETION 1 0 0 0
ASYNC_IO_COMPLETION 0 0 0 0
ASYNC_NETWORK_IO 40 15 15 0
SLEEP_BPOOL_FLUSH 0 0 0 0
CHKPT 0 0 0 0
SLEEP_TASK 725 0 0 0
SLEEP_SYSTEMTASK 0 0 0 0
RESOURCE_SEMAPHORE 0 0 0 0
DTC 0 0 0 0
OLEDB 44 0 0 0
FAILPOINT 0 0 0 0
RESOURCE_QUEUE 0 0 0 0
ASYNC_DISKPOOL_LOCK 0 0 0 0
THREADPOOL 0 0 0 0
DEBUG 0 0 0 0
REPLICA_WRITES 0 0 0 0
BROKER_RECEIVE_WAITFOR 5 25000 5000 0
DBMIRRORING_CMD 0 0 0 0
WAIT_FOR_RESULTS 0 0 0 0
SOS_SCHEDULER_YIELD 93 0 0 0
SOS_VIRTUALMEMORY_LOW 0 0 0 0
SOS_RESERVEDMEMBLOCKLIST 0 0 0 0
SOS_LOCALALLOCATORLIST 0 0 0 0
SOS_CALLBACK_REMOVAL 0 0 0 0
LOWFAIL_MEMMGR_QUEUE 0 0 0 0
BACKUP 0 0 0 0
BACKUPBUFFER 0 0 0 0
BACKUPIO 0 0 0 0
BACKUPTHREAD 0 0 0 0
DBMIRROR_DBM_MUTEX 0 0 0 0
DBMIRROR_DBM_EVENT 0 0 0 0
DBMIRROR_SEND 0 0 0 0
CURSOR_ASYNC 0 0 0 0
HTTP_ENUMERATION 0 0 0 0
SOAP_READ 0 0 0 0
SOAP_WRITE 0 0 0 0
DUMP_LOG_COORDINATOR 0 0 0 0
DISKIO_SUSPEND 0 0 0 0
IMPPROV_IOWAIT 0 0 0 0
QNMANAGER_ACQUIRE 0 0 0 0
DEADLOCK_TASK_SEARCH 0 0 0 0
REPL_SCHEMA_ACCESS 0 0 0 0
REPL_CACHE_ACCESS 0 0 0 0
SQLSORT_SORTMUTEX 0 0 0 0
SQLSORT_NORMMUTEX 0 0 0 0
SQLTRACE_WAIT_ENTRIES 0 0 0 0
SQLTRACE_LOCK 1 0 0 0
SQLTRACE_BUFFER_FLUSH 7 28000 4000 0
SQLTRACE_SHUTDOWN 0 0 0 0
MSQL_SYNC_PIPE 0 0 0 0
QUERY_TRACEOUT 0 0 0 0
DTC_STATE 0 0 0 0
FCB_REPLICA_WRITE 0 0 0 0
FCB_REPLICA_READ 0 0 0 0
WRITELOG 21 0 0 0
HTTP_ENDPOINT_COLLCREATE 0 0 0 0
EXCHANGE 0 0 0 0
DBTABLE 0 0 0 0
EC 0 0 0 0
TEMPOBJ 0 0 0 0
XACTLOCKINFO 0 0 0 0
LOGMGR 0 0 0 0
CMEMTHREAD 0 0 0 0
CXPACKET 30 125 46 0
WAITFOR 0 0 0 0
CURSOR 0 0 0 0
EXECSYNC 0 0 0 0
SOSHOST_INTERNAL 0 0 0 0
SOSHOST_SLEEP 0 0 0 0
SOSHOST_WAITFORDONE 0 0 0 0
SOSHOST_MUTEX 0 0 0 0
SOSHOST_EVENT 0 0 0 0
SOSHOST_SEMAPHORE 0 0 0 0
SOSHOST_RWLOCK 0 0 0 0
SOSHOST_TRACELOCK 0 0 0 0
MSQL_XP 0 0 0 0
MSQL_DQ 0 0 0 0
LOGBUFFER 0 0 0 0
TRANSACTION_MUTEX 0 0 0 0
MSSEARCH 0 0 0 0
XACTWORKSPACE_MUTEX 0 0 0 0
CLR_JOIN 0 0 0 0
CLR_CRST 0 0 0 0
CLR_SEMAPHORE 0 0 0 0
CLR_M
TRACEYSQL
New Member
New Member

--
17 Jul 2008 01:01 PM
Im on a 8 processor so i am going to change it to one processor to see if it stuck on the parallelism..
TRACEYSQL
New Member
New Member

--
17 Jul 2008 05:17 PM
There is nothing in the trace files or the waits that is leading to a problem at all - thats the problem the waits are not telling me nor is it pointing to a problem with the CPU - nor is it problem with indexes - i have analyzed everything - now it is back to the vendor to look at.

I let you know if i find anything with it - the problem is there is code that is hidden and not always in the SQL Profiler.

TRACEYSQL
New Member
New Member

--
17 Jul 2008 06:04 PM
Ok changed a few things on profiler was just getting this
exec sp_cursorfetch 180156759,16,529,
Not really help

Now got SP: Starting and STMTStarting.

Gives me the SP thats good.

Hope that enough so i can see the loop it is stuck in or something.







TRACEYSQL
New Member
New Member

--
18 Jul 2008 05:47 AM
Just read the threads - they were very good.
No point doing one waits before - i need to run it first clear it out then run my waits again after wards or at least keep it executing for a few minutes whilst process runs then do a compare before and after to see what going on.

Definately like to know how to do for the one particular process if that is at all possible and track whether it first is getting some information from memory then going off to get the reset from disk there forefore putting this into a wait process - Would that be too specific.

TRACEYSQL
New Member
New Member

--
18 Jul 2008 07:47 AM
Ok now got my entire waits cleared out first then captured it.

These are what going on inside of the SQL BOX PHYSICAL IO which is where data is moved from cpu to memory.

So the HEADINGS
Request is how many occured in the time period
Total Wait Time MS (How long it waits)
MAX WAIT TIME (MS) ---This is how long one particular thread waited?
%Wait is just in percentage.

Could you help explain this part
Resource Waits MS
%RES WAITS
Signal wait ms
%Signal Wait MS

Reading along and getting bit lost on the 2nd set of values.

If i grasp all this i think i be on the right tracks here.


Requests Total Wait Times ms Max Wait Time MS % Waits Resource Waits ms % Res Waits Sign Wait ms % Signal Waits
***Total*** 23433698 2640025 0 100 2505046 100 134979 100
CXPACKET 150666 845203 7843 32 792532 31.6 52671 39
BROKER_TASK_STOP 98 490000 5000 18.6 490000 19.6 0 0
ASYNC_NETWORK_IO 206645 447703 1531 17 426016 17 21687 16.1
BROKER_RECEIVE_WAITFOR 90 445000 5000 16.9 445000 17.8 0 0
PAGEIOLATCH_SH 9474 180031 1125 6.8 177500 7.1 2531 1.9
OLEDB 22588094 92000 9968 3.5 92000 3.7 0 0
SOS_SCHEDULER_YIELD 144678 36859 859 1.4 109 0 36750 27.2
WRITELOG 13007 26531 1203 1 24672 1 1859 1.4
LATCH_EX 7651 20781 31 0.8 18156 0.7 2625 1.9
PAGELATCH_EX 283159 16484 187 0.6 656 0 15828 11.7
PAGEIOLATCH_EX 2706 11390 859 0.4 11312 0.5 78 0.1
IO_COMPLETION 18918 10328 234 0.4 9969 0.4 359 0.3
LCK_M_S 2 9328 7734 0.4 9328 0.4 0 0
SLEEP_BPOOL_FLUSH 3272 3718 15 0.1 3672 0.1 46 0
SQLTRACE_LOCK 77 1562 390 0.1 1562 0.1 0 0
LATCH_SH 666 906 31 0 578 0 328 0.2
EXECSYNC 23 765 109 0 765 0 0 0
LOGBUFFER 16 500 203 0 500 0 0 0
MSQL_XP 146 250 46 0 250 0 0 0
PAGELATCH_UP 72 203 93 0 188 0 15 0
LCK_M_IS 1 140 140 0 140 0 0 0
PAGEIOLATCH_UP 73 109 31 0 94 0 15 0
PAGELATCH_SH 1708 109 15 0 0 0 109 0.1
IO_AUDIT_MUTEX 2105 78 15 0 47 0 31 0
CMEMTHREAD 348 46 15 0 0 0 46 0
TRACEYSQL
New Member
New Member

--
18 Jul 2008 09:50 AM
Getting there - now just run same process on our test where it works the major thing i noticed is the test is using named pipes and prod is using TCP/IP and that where it times out.

Hmmm now where am i ...golly ....

TCP Time outs settings reached - gosh this getting way out of my depth.

TRACEYSQL
New Member
New Member

--
18 Jul 2008 04:07 PM
Your telling me.......i confirmed out test put TCP/IP and viola the app financials just booms out...Only works with named pipes who would have thought that ha.

Put the named pipes in production and viola everything works a treat.

Now we either got a TCP/IP problem where it not holding the sessions open or exhausted them....that now up to the network guys to fix.

Golly what a long time this took to see......

As long as our long processes can run im happy but it should work with TCP/IP so i finally got somewhere.

Now back to the wait statistics.
TRACEYSQL
New Member
New Member

--
19 Jul 2008 06:54 AM
We have 8 processors - i have to check if that is dual? i see 8 CPU in task manager.

MAXDOP at the server level set to the default of 0 .
Setting is max degree of parallelism 0 64 0 0
So i should change to the 0 to 8.

For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node

How to tell if NUMA configured and how to tell how many CPU assigned to a NUMA.



SUMMARY
This article discusses the general guidelines that you use to configure the max degree of parallelism (MAXDOP) option for SQL Server when you use the sp_configure system stored procedure.

Additionally, the OPTION (MAXDOP 8) Transact-SQL query hints can override the max degree of parallelism configuration option in sp_configure only for the query that specifies this option. The override takes affect only if the value that is specified in the hint is less than or equal to the sp_configure value. All semantic rules that are used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint.

To understand parallelism, first read the material that the "References" section points to.
Back to the top

MORE INFORMATION
Note The MAXDOP option does not limit the number of processors that SQL Server uses. Use the affinity mask configuration option to configure the number of processors that SQL Server uses.

Use the following guidelines when you configure the MAXDOP value: • For servers that use more than eight processors, use the following configuration: MAXDOP=8.
• For servers that have eight or less processors, use the following configuration where N equals the number of processors: MAXDOP=0 to N.
• For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
• For servers that have hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors.
These are general guidelines only. There may be some exceptions to these guidelines. For example, do not use the guidelines under the following conditions: • If the server is not an online transaction processing (OLTP)-based server.
• If the server is running large batch processing when there is no or minimal user load.
• If the server is running resource-intensive operations.
Note Test the configuration before you implement the changes on a production server.

The recommendations in this article are based on the following.
TRACEYSQL
New Member
New Member

--
19 Jul 2008 06:10 PM
Ok - got all my waits reports firing now - running every hour . 7-9 PM then at 10 i clear them out for the next day so i have daily waits stats of what going on.

Should start giving me a good overall on it.

Yes i did read the MAXDOP I didn't realize it used all processors and this would leave none for other processors to run. Thanks for the tip.

Sometimes i get the mag - i think i only had two of them since i signed up.

TRACEYSQL
New Member
New Member

--
21 Jul 2008 08:09 AM
Appreciate your help Thanks -
let me know when your training classes are im sure i could learn more from someone like you in a few hours then reading books or attending classes .



TRACEYSQL
New Member
New Member

--
22 Jul 2008 12:24 PM
Hi there i have the waits going on for three days now.
Each night i reset the clear so i have an entire days worth.

When i run the report_wait_stats_2005
It says start time of 2008-07-19 until 2008-07-22

Tried to put in parameter of 2008-07-22 15:00:00.250 still same.

Is there a way to put start and end time only so i get entire day rather than 3 days.
TRACEYSQL
New Member
New Member

--
22 Jul 2008 01:36 PM
Yes but sometimes it is the next day - ok i look to add start time too.
TRACEYSQL
New Member
New Member

--
23 Jul 2008 08:14 AM
Great got my hour report done.

Interesting results now

It is between the two
IO COMPLETION 25% and SOS_SCHEDULER 41%
IO COMPLETION 58% and SOS_SCHEDULER 29%

Now at 15:00 to 16:00 a hugh ASYNC NETWORK IO of 99% came in for both days.
Do i see a pattern here - wonder what goes on around this time. Do have alaska people coming in hmm i wonder.

Now to chase the above down and figure out some more things.

CPU (processor affinity and io affinity are all set to equal 8 CPU i have).
processor affinity is the actual works and
io affinity is thinks like checkpoint, etc internal sql processes.

(Is this something to start looking at and having all system processors on one CPU and freeing up the CPU for workers?)

TRACEYSQL
New Member
New Member

--
24 Jul 2008 06:55 AM
Yes im not changing anything see on research and analysis mode.
I going to find out what queries were run at time etc.
and acpture from dm_os_schedulers to see if really I/O Bound or just my wonderful users keep running their yearly reports like they do.


You are not authorized to post a reply.

Acceptable Use Policy