Search Results

Search found 890 results on 36 pages for 'jonathan kehayias'.

Page 1/36 | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

  • SQL SERVER – Guest Post – Jonathan Kehayias – Wait Type – Day 16 of 28

    - by pinaldave
    Jonathan Kehayias (Blog | Twitter) is a MCITP Database Administrator and Developer, who got started in SQL Server in 2004 as a database developer and report writer in the natural gas industry. After spending two and a half years working in TSQL, in late 2006, he transitioned to the role of SQL Database Administrator. His primary passion is performance tuning, where he frequently rewrites queries for better performance and performs in depth analysis of index implementation and usage. Jonathan blogs regularly on SQLBlog, and was a coauthor of Professional SQL Server 2008 Internals and Troubleshooting. On a personal note, I think Jonathan is extremely positive person. In every conversation with him I have found that he is always eager to help and encourage. Every time he finds something needs to be approved, he has contacted me without hesitation and guided me to improve, change and learn. During all the time, he has not lost his focus to help larger community. I am honored that he has accepted to provide his views on complex subject of Wait Types and Queues. Currently I am reading his series on Extended Events. Here is the guest blog post by Jonathan: SQL Server troubleshooting is all about correlating related pieces of information together to indentify where exactly the root cause of a problem lies. In my daily work as a DBA, I generally get phone calls like, “So and so application is slow, what’s wrong with the SQL Server.” One of the funny things about the letters DBA is that they go so well with Default Blame Acceptor, and I really wish that I knew exactly who the first person was that pointed that out to me, because it really fits at times. A lot of times when I get this call, the problem isn’t related to SQL Server at all, but every now and then in my initial quick checks, something pops up that makes me start looking at things further. The SQL Server is slow, we see a number of tasks waiting on ASYNC_IO_COMPLETION, IO_COMPLETION, or PAGEIOLATCH_* waits in sys.dm_exec_requests and sys.dm_exec_waiting_tasks. These are also some of the highest wait types in sys.dm_os_wait_stats for the server, so it would appear that we have a disk I/O bottleneck on the machine. A quick check of sys.dm_io_virtual_file_stats() and tempdb shows a high write stall rate, while our user databases show high read stall rates on the data files. A quick check of some performance counters and Page Life Expectancy on the server is bouncing up and down in the 50-150 range, the Free Page counter consistently hits zero, and the Free List Stalls/sec counter keeps jumping over 10, but Buffer Cache Hit Ratio is 98-99%. Where exactly is the problem? In this case, which happens to be based on a real scenario I faced a few years back, the problem may not be a disk bottleneck at all; it may very well be a memory pressure issue on the server. A quick check of the system spec’s and it is a dual duo core server with 8GB RAM running SQL Server 2005 SP1 x64 on Windows Server 2003 R2 x64. Max Server memory is configured at 6GB and we think that this should be enough to handle the workload; or is it? This is a unique scenario because there are a couple of things happening inside of this system, and they all relate to what the root cause of the performance problem is on the system. If we were to query sys.dm_exec_query_stats for the TOP 10 queries, by max_physical_reads, max_logical_reads, and max_worker_time, we may be able to find some queries that were using excessive I/O and possibly CPU against the system in their worst single execution. We can also CROSS APPLY to sys.dm_exec_sql_text() and see the statement text, and also CROSS APPLY sys.dm_exec_query_plan() to get the execution plan stored in cache. Ok, quick check, the plans are pretty big, I see some large index seeks, that estimate 2.8GB of data movement between operators, but everything looks like it is optimized the best it can be. Nothing really stands out in the code, and the indexing looks correct, and I should have enough memory to handle this in cache, so it must be a disk I/O problem right? Not exactly! If we were to look at how much memory the plan cache is taking by querying sys.dm_os_memory_clerks for the CACHESTORE_SQLCP and CACHESTORE_OBJCP clerks we might be surprised at what we find. In SQL Server 2005 RTM and SP1, the plan cache was allowed to take up to 75% of the memory under 8GB. I’ll give you a second to go back and read that again. Yes, you read it correctly, it says 75% of the memory under 8GB, but you don’t have to take my word for it, you can validate this by reading Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2. In this scenario the application uses an entirely adhoc workload against SQL Server and this leads to plan cache bloat, and up to 4.5GB of our 6GB of memory for SQL can be consumed by the plan cache in SQL Server 2005 SP1. This in turn reduces the size of the buffer cache to just 1.5GB, causing our 2.8GB of data movement in this expensive plan to cause complete flushing of the buffer cache, not just once initially, but then another time during the queries execution, resulting in excessive physical I/O from disk. Keep in mind that this is not the only query executing at the time this occurs. Remember the output of sys.dm_io_virtual_file_stats() showed high read stalls on the data files for our user databases versus higher write stalls for tempdb? The memory pressure is also forcing heavier use of tempdb to handle sorting and hashing in the environment as well. The real clue here is the Memory counters for the instance; Page Life Expectancy, Free List Pages, and Free List Stalls/sec. The fact that Page Life Expectancy is fluctuating between 50 and 150 constantly is a sign that the buffer cache is experiencing constant churn of data, once every minute to two and a half minutes. If you add to the Page Life Expectancy counter, the consistent bottoming out of Free List Pages along with Free List Stalls/sec consistently spiking over 10, and you have the perfect memory pressure scenario. All of sudden it may not be that our disk subsystem is the problem, but is instead an innocent bystander and victim. Side Note: The Page Life Expectancy counter dropping briefly and then returning to normal operating values intermittently is not necessarily a sign that the server is under memory pressure. The Books Online and a number of other references will tell you that this counter should remain on average above 300 which is the time in seconds a page will remain in cache before being flushed or aged out. This number, which equates to just five minutes, is incredibly low for modern systems and most published documents pre-date the predominance of 64 bit computing and easy availability to larger amounts of memory in SQL Servers. As food for thought, consider that my personal laptop has more memory in it than most SQL Servers did at the time those numbers were posted. I would argue that today, a system churning the buffer cache every five minutes is in need of some serious tuning or a hardware upgrade. Back to our problem and its investigation: There are two things really wrong with this server; first the plan cache is excessively consuming memory and bloated in size and we need to look at that and second we need to evaluate upgrading the memory to accommodate the workload being performed. In the case of the server I was working on there were a lot of single use plans found in sys.dm_exec_cached_plans (where usecounts=1). Single use plans waste space in the plan cache, especially when they are adhoc plans for statements that had concatenated filter criteria that is not likely to reoccur with any frequency.  SQL Server 2005 doesn’t natively have a way to evict a single plan from cache like SQL Server 2008 does, but MVP Kalen Delaney, showed a hack to evict a single plan by creating a plan guide for the statement and then dropping that plan guide in her blog post Geek City: Clearing a Single Plan from Cache. We could put that hack in place in a job to automate cleaning out all the single use plans periodically, minimizing the size of the plan cache, but a better solution would be to fix the application so that it uses proper parameterized calls to the database. You didn’t write the app, and you can’t change its design? Ok, well you could try to force parameterization to occur by creating and keeping plan guides in place, or we can try forcing parameterization at the database level by using ALTER DATABASE <dbname> SET PARAMETERIZATION FORCED and that might help. If neither of these help, we could periodically dump the plan cache for that database, as discussed as being a problem in Kalen’s blog post referenced above; not an ideal scenario. The other option is to increase the memory on the server to 16GB or 32GB, if the hardware allows it, which will increase the size of the plan cache as well as the buffer cache. In SQL Server 2005 SP1, on a system with 16GB of memory, if we set max server memory to 14GB the plan cache could use at most 9GB  [(8GB*.75)+(6GB*.5)=(6+3)=9GB], leaving 5GB for the buffer cache.  If we went to 32GB of memory and set max server memory to 28GB, the plan cache could use at most 16GB [(8*.75)+(20*.5)=(6+10)=16GB], leaving 12GB for the buffer cache. Thankfully we have SQL Server 2005 Service Pack 2, 3, and 4 these days which include the changes in plan cache sizing discussed in the Changes to Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2 blog post. In real life, when I was troubleshooting this problem, I spent a week trying to chase down the cause of the disk I/O bottleneck with our Server Admin and SAN Admin, and there wasn’t much that could be done immediately there, so I finally asked if we could increase the memory on the server to 16GB, which did fix the problem. It wasn’t until I had this same problem occur on another system that I actually figured out how to really troubleshoot this down to the root cause.  I couldn’t believe the size of the plan cache on the server with 16GB of memory when I actually learned about this and went back to look at it. SQL Server is constantly telling a story to anyone that will listen. As the DBA, you have to sit back and listen to all that it’s telling you and then evaluate the big picture and how all the data you can gather from SQL about performance relate to each other. One of the greatest tools out there is actually a free in the form of Diagnostic Scripts for SQL Server 2005 and 2008, created by MVP Glenn Alan Berry. Glenn’s scripts collect a majority of the information that SQL has to offer for rapid troubleshooting of problems, and he includes a lot of notes about what the outputs of each individual query might be telling you. When I read Pinal’s blog post SQL SERVER – ASYNC_IO_COMPLETION – Wait Type – Day 11 of 28, I noticed that he referenced Checking Memory Related Performance Counters in his post, but there was no real explanation about why checking memory counters is so important when looking at an I/O related wait type. I thought I’d chat with him briefly on Google Talk/Twitter DM and point this out, and offer a couple of other points I noted, so that he could add the information to his blog post if he found it useful.  Instead he asked that I write a guest blog for this. I am honored to be a guest blogger, and to be able to share this kind of information with the community. The information contained in this blog post is a glimpse at how I do troubleshooting almost every day of the week in my own environment. SQL Server provides us with a lot of information about how it is running, and where it may be having problems, it is up to us to play detective and find out how all that information comes together to tell us what’s really the problem. This blog post is written by Jonathan Kehayias (Blog | Twitter). Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: MVP, Pinal Dave, PostADay, Readers Contribution, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Wait Stats, SQL Wait Types, T SQL, Technology

    Read the article

  • Le pluriel dans les traductions avec Qt, un article de Jan-Arve Sæther traduit par Jonathan Courtois

    Bonjour, Dans la continuité des traductions des Qt Quarterly, je vous propose aujourd'hui un article expliquant comment gérer au mieux les formes du pluriel des différentes langues dans vos traductions. Les formes du pluriel dans les traductions Aviez-vous déjà connaissance de ces subtilités ? Cet article va-t-il améliorer la qualité des traductions de vos applications ?...

    Read the article

  • PCSX2 1.0.0 installation issues

    - by user205261
    I've followed all the tutorials I can find to get this emulator to work and I'm now getting this error. Granted I've gotten a lot further than when I started. Running 13.10. Just downloaded the Linux download file from the pcsx2 website and did the following in terminal: jonathan@Assassin:~$ sudo add-apt-repository ppa:gregory-hainaut/pcsx2.official.ppa [sudo] password for jonathan: The Official pcsx2 ppa, provided by the pcsx2 team. This ppa contains a regular package snapshot of pcsx2. We are not package experts, but we try to follow the debian policy. Press [ENTER] to continue or ctrl-c to cancel adding it gpg: keyring `/tmp/tmpe5fwdz/secring.gpg' created gpg: keyring `/tmp/tmpe5fwdz/pubring.gpg' created gpg: requesting key 7A617FF4 from hkp server keyserver.ubuntu.com gpg: /tmp/tmpe5fwdz/trustdb.gpg: trustdb created gpg: key 7A617FF4: public key "Launchpad official ppa for pcsx2 team" imported gpg: Total number processed: 1 gpg: imported: 1 (RSA: 1) OK jonathan@Assassin:~$ sudo apt-get update Err http://ppa.launchpad.net saucy/main amd64 Packages 404 Not Found Err http://ppa.launchpad.net saucy/main i386 Packages 404 Not Found Fetched 67.4 kB in 16s (3,977 B/s) W: Failed to fetch ppa.launchpad.net/gregory-hainaut/pcsx2.official.ppa/ubuntu/dists/saucy/main/binary-amd64/Packages 404 Not Found W: Failed to fetch ppa.launchpad.net/gregory-hainaut/pcsx2.official.ppa/ubuntu/dists/saucy/main/binary-i386/Packages 404 Not Found E: Some index files failed to download. They have been ignored, or old ones used instead. jonathan@Assassin:~$ sudo apt-get install pcsx2 Reading package lists... Done Building dependency tree Reading state information... Done E: Unable to locate package pcsx2 So I'm assuming I just need the way to get the two missing packages?

    Read the article

  • Apple Script error: Can't get Folder

    - by Jonathan Hirsch
    I started using apple script today, because I wanted to be able to automatically send files to someone when they go into a specific folder. I first tried with automator, but the files were never attached into emails, so i figured i could try with apple script. At first, I tried simply create an email with an attached file. The email was created, but the file was not attached. After a while, I stumbled onto this post that was trying to list files from a folder, so I tried that just to compare it to my code. It gave me an error telling me it is impossible to list files from that folder. So I tried setting a path to a specific folder, and I got an error saying the path can't be made. This is the code I used for the last part: tell application "Finder" set folderPath to folder "Macintosh HD: Users:Jonathan:Desktop:Send_Mail" set fileList to name of every file in folderPath end tell and this is the error I got. error "Finder got an error: Can’t get folder \"Macintosh HD: Users:Jonathan:Desktop:Send_Mail\"." number -1728 from folder "Macintosh HD: Users:Jonathan:Desktop:Send_Mail". I later tried with another folder, and I always get this error, even when using the Users folder for example. Any Suggestions? thanks

    Read the article

  • How can I install Satchmo?

    - by Jonathan Hayward
    I am trying to install Satchmo 0.9 on an Ubuntu 9.10 guest off of the instructions at http://bitbucket.org/chris1610/satchmo/downloads/Satchmo.pdf. I run into difficulties at 2.1.2: pip install -r http://bitbucket.org/chris1610/satchmo/raw/tip/scripts/requirements.txt pip install -e hg+http://bitbucket.org/chris1610/satchmo/@v0.9#egg=satchmo The first command fails because a compile error for how it's trying to build PIL. So I ran an "aptitude install python-imaging", locally copy the first line's requirements.text, and remove the line that's unsuccessfully trying to build PIL. The first line completes without error, as does the second. The next step tells me to change directory to the /path/to/new/store, and run: python clonesatchmo.py A little bit of trouble here; I am told that clonesatchmo.py will be in /bin by now, and it isn't there, but I put some Satchmo stuff under /usr/local, create a symlink in /bin, and run: python /bin/clonesatchmo.py This gives: jonathan@ubuntu:~/store$ python /bin/clonesatchmo.py Creating the Satchmo Application Traceback (most recent call last): File "/bin/clonesatchmo.py", line 108, in <module> create_satchmo_site(opts.site_name) File "/bin/clonesatchmo.py", line 47, in create_satchmo_site import satchmo_skeleton ImportError: No module named satchmo_skeleton A find after apparently checking out the repository reveals that there is no file with a name like satchmo*skeleton* on my system. I thought that bash might be prone to take part of the second pip invocation's URL as the beginning of a comment; I tried both: pip install -e hg+http://bitbucket.org/chris1610/satchmo/@v0.9\#egg=satchmo pip install -e hg+http://bitbucket.org/chris1610/satchmo/@v0.9#egg=satchmo Neither way of doing it seems to take care of the import error mentioned above. How can I get a Satchmo installation under Ubuntu, or at least enough of a Satchmo installation that I am able to start with a skeleton of a store and then flesh it out the way I want? Thanks, Jonathan

    Read the article

  • How can I install Satchmo?

    - by Jonathan Hayward
    I am trying to install Satchmo 0.9 on an Ubuntu 9.10 32-bit guest off of the instructions at http://bitbucket.org/chris1610/satchmo/downloads/Satchmo.pdf. I run into difficulties at 2.1.2: pip install -r http://bitbucket.org/chris1610/satchmo/raw/tip/scripts/requirements.txt pip install -e hg+http://bitbucket.org/chris1610/satchmo/@v0.9#egg=satchmo The first command fails because a compile error for how it's trying to build PIL. So I ran an "aptitude install python-imaging", locally copy the first line's requirements.text, and remove the line that's unsuccessfully trying to build PIL. The first line completes without reported error, as does the second. The next step tells me to change directory to the /path/to/new/store, and run: python clonesatchmo.py A little bit of trouble here; I am told that clonesatchmo.py will be in /bin by now, and it isn't there, but I put some Satchmo stuff under /usr/local, create a symlink in /bin, and run: python /bin/clonesatchmo.py This gives: jonathan@ubuntu:~/store$ python /bin/clonesatchmo.py Creating the Satchmo Application Traceback (most recent call last): File "/bin/clonesatchmo.py", line 108, in <module> create_satchmo_site(opts.site_name) File "/bin/clonesatchmo.py", line 47, in create_satchmo_site import satchmo_skeleton ImportError: No module named satchmo_skeleton A find after apparently checking out the repository reveals that there is no file with a name like satchmo*skeleton* on my system. I thought that bash might be prone to take part of the second pip invocation's URL as the beginning of a comment; I tried both: pip install -e hg+http://bitbucket.org/chris1610/satchmo/@v0.9\#egg=satchmo pip install -e hg+http://bitbucket.org/chris1610/satchmo/@v0.9#egg=satchmo Neither way of doing it seems to take care of the import error mentioned above. How can I get a Satchmo installation under Ubuntu, or at least enough of a Satchmo installation that I am able to start with a skeleton of a store and then flesh it out the way I want? Thanks, Jonathan

    Read the article

  • Free eBook "Troubleshooting SQL Server: A Guide for the Accidental DBA"

    - by TATWORTH
    "SQL Server-related performance problems come up regularly and diagnosing and solving them can be difficult and time consuming. Read SQL Server MVP Jonathan Kehayias’ Troubleshooting SQL Server: A Guide for the Accidental DBA for descriptions of the most common issues and practical solutions to fix them quickly and accurately." Please go to http://www.red-gate.com/products/dba/sql-monitor/entrypage/tame-unruly-sql-servers-ebook RedGate produce some superb tools for SQL Server. Jonathan's book is excellent - I commend it to all SQL DBA and developers.

    Read the article

  • dazzle DVC100 in live movie maker

    - by Jonathan
    How can I use the Pinnacle Dazzle (it is a USB device which lets you plug in a video player or other analogue source) with Windows live movie maker (because it is just simple and I don't need effects/editing etc) Or is there a way I can use it on my Macbook Pro? Jonathan

    Read the article

  • Is pdf virus proof?

    - by Jonathan
    I am creating a secure document portal for a client. One of the things that they are worried about is having someone upload a document with a virus. One solution that might work is to enforce that they only upload pdf files. My question is two fold: Most importantly, is a pdf document virus proof? How can you determine in a *nix environment that a file is a pdf, besides just looking at the extension. Thanks! Jonathan

    Read the article

  • Ssh, run a command on login, and then Stay Logged In?

    - by jonathan
    I tried this with expect, but it didn't work: it closed the connection at the end. Can we run a script via ssh which will log into remote machines, run a command, and not disconnect? So ssh in a machine, cd to such and such a directory, and then run a command, and stay logged in. -Jonathan (expect I used) #!/usr/bin/expect -f set password [lrange $argv 0 0] spawn ssh root@marlboro "cd /tmp; ls -altr | tail" expect "?assword:*" send -- "$password\r" send -- "\r" interact

    Read the article

  • Network is not working anymore - Ubuntu 12.04

    - by Jonathan
    Network is not working anymore - Ubuntu 12.04 Hello, I have a problem with my network connection. I have been using the same laptop with Ubuntu and the same connection for more than a year, and suddenly yesterday the connection stopped working (both wireless and wired). I've tested with another computer and the connection is fine (both wireless and wired). I've been reading similar posts but I haven't found a solution yet. I tried a few commands that I'm posting here (my system is in spanish, so I have traslated it to english, maybe the terms are not accurate): grep -i eth /var/log/syslog | tail Jun 3 18:45:40 vanesa-pc NetworkManager[3584]: (eth0): now managed Jun 3 18:45:40 vanesa-pc NetworkManager[3584]: (eth0): device state change: unmanaged - unavailable (reason 'managed') [10 20 2] Jun 3 18:45:40 vanesa-pc NetworkManager[3584]: (eth0): bringing up device. Jun 3 18:45:40 vanesa-pc NetworkManager[3584]: (eth0): preparing device. Jun 3 18:45:40 vanesa-pc kernel: [ 7351.845743] forcedeth 0000:00:0a.0: irq 41 for MSI/MSI-X Jun 3 18:45:40 vanesa-pc kernel: [ 7351.845984] forcedeth 0000:00:0a.0: eth0: no link during initialization Jun 3 18:45:40 vanesa-pc kernel: [ 7351.847103] ADDRCONF(NETDEV_UP): eth0: link is not ready Jun 3 18:45:40 vanesa-pc NetworkManager[3584]: (eth0): deactivating device (reason 'managed') [2] Jun 3 18:45:40 vanesa-pc NetworkManager[3584]: Added default wired connection 'Wired connection 1' for /sys/devices/pci0000:00/0000:00:0a.0/net/eth0 Jun 3 18:45:40 vanesa-pc kernel: [ 7351.848817] ADDRCONF(NETDEV_UP): eth0: link is not ready ifconfig -a eth0 Link encap:Ethernet addressHW 00:1b:24:fc:a8:d1 ACTIVE BROADCAST MULTICAST MTU:1500 Metric:1 Packages RX:0 errors:16 lost:0 overruns:0 frame:16 Packages TX:123 errors:0 lost:0 overruns:0 carrier:0 colissions:0 length.tailTX:1000 Bytes RX:0 (0.0 B) TX bytes:26335 (26.3 KB) Interruption:41 Base address: 0x2000 lo Link encap:Local loop Inet address:127.0.0.1 Mask:255.0.0.0 Inet6 address: ::1/128 Scope:Host ACTIVE LOOP WORKING MTU:16436 Metrics:1 Packages RX:1550 errors:0 lost:0 overruns:0 frame:0 Packages TX:1550 errors:0 lost:0 overruns:0 carrier:0 colissions:0 long.tailTX:0 Bytes RX:125312 (125.3 KB) TX bytes:125312 (125.3 KB) iwconfig lo no wireless extensions. eth0 no wireless extensions. sudo lshw -C network *-network description: Ethernet interface product: MCP67 Ethernet manufacturer: NVIDIA Corporation Physical id: a bus information: pci@0000:00:0a.0 logical name: eth0 version: a2 series: 00:1b:24:fc:a8:d1 capacity: 100Mbit/s width: 32 bits clock: 66MHz capacities: pm msi ht bus_master cap_list ethernet physical mii 10bt 10bt-fd 100bt 100bt-fd autonegotiation configuration: autonegotiation=on broadcast=yes driver=forcedeth driverversion=0.64 latency=0 link=no maxlatency=20 mingnt=1 multicast=yes port=MII resources: irq:41 memoria:f6288000-f6288fff ioport:30f8(size=8) memoria:f6289c00-f6289cff memoria:f6289800-f628980f lsmod Module Size Used by usbhid 41906 0 hid 77367 1 usbhid rfcomm 38139 0 parport_pc 32114 0 ppdev 12849 0 bnep 17830 2 bluetooth 158438 10 rfcomm,bnep binfmt_misc 17292 1 joydev 17393 0 hp_wmi 13652 0 sparse_keymap 13658 1 hp_wmi nouveau 708198 3 ttm 65344 1 nouveau drm_kms_helper 45466 1 nouveau drm 197692 5 nouveau,ttm,drm_kms_helper i2c_algo_bit 13199 1 nouveau psmouse 87213 0 mxm_wmi 12859 1 nouveau serio_raw 13027 0 k8temp 12905 0 i2c_nforce2 12906 0 wmi 18744 2 hp_wmi,mxm_wmi video 19068 1 nouveau mac_hid 13077 0 lp 17455 0 parport 40930 3 parport_pc,ppdev,lp forcedeth 58096 0 Let me know if I can give you more information. Thank you very much in advance, Jonathan

    Read the article

  • A quick look at: sys.dm_os_buffer_descriptors

    - by Jonathan Allen
    SQL Server places data into cache as it reads it from disk so as to speed up future queries. This dmv lets you see how much data is cached at any given time and knowing how this changes over time can help you ensure your servers run smoothly and are adequately resourced to run your systems. This dmv gives the number of cached pages in the buffer pool along with the database id that they relate to: USE [tempdb] GO SELECT COUNT(*) AS cached_pages_count , CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE DB_NAME(database_id) END AS Database_name FROM sys.dm_os_buffer_descriptors GROUP BY DB_NAME(database_id) , database_id ORDER BY cached_pages_count DESC; This gives you results which are quite useful, but if you add a new column with the code: …to convert the pages value to show a MB value then they become more relevant and meaningful. To see how your server reacts to queries, start up SSMS and connect to a test server and database – mine is called AdventureWorks2008. Make sure you start from a know position by running: -- Only run this on a test server otherwise your production server's-- performance may drop off a cliff and your phone will start ringing. DBCC DROPCLEANBUFFERS GO Now we can run a query that would normally turn a DBA’s hair white: USE [AdventureWorks2008] go SELECT * FROM [Sales].[SalesOrderDetail] AS sod INNER JOIN [Sales].[SalesOrderHeader] AS soh ON [sod].[SalesOrderID] = [soh].[SalesOrderID] …and then check our cache situation: A nice low figure – not! Almost 2000 pages of data in cache equating to approximately 15MB. Luckily these tables are quite narrow; if this had been on a table with more columns then this could be even more dramatic. So, let’s make our query more efficient. After resetting the cache with the DROPCLEANBUFFERS and FREEPROCCACHE code above, we’ll only select the columns we want and implement a WHERE predicate to limit the rows to a specific customer. SELECT [sod].[OrderQty] , [sod].[ProductID] , [soh].[OrderDate] , [soh].[CustomerID] FROM [Sales].[SalesOrderDetail] AS sod INNER JOIN [Sales].[SalesOrderHeader] AS soh ON [sod].[SalesOrderID] = [soh].[SalesOrderID] WHERE [soh].[CustomerID] = 29722 …and check our effect cache: Now that is more sympathetic to our server and the other systems sharing its resources. I can hear you asking: “What has this got to do with logging, Jonathan?” Well, a smart DBA will keep an eye on this metric on their servers so they know how their hardware is coping and be ready to investigate anomalies so that no ‘disruptive’ code starts to unsettle things. Capturing this information over a period of time can lead you to build a picture of how a database relies on the cache and how it interacts with other databases. This might allow you to decide on appropriate schedules for over night jobs or otherwise balance the work of your server. You could schedule this job to run with a SQL Agent job and store the data in your DBA’s database by creating a table with: IF OBJECT_ID('CachedPages') IS NOT NULL DROP TABLE CachedPages CREATE TABLE CachedPages ( cached_pages_count INT , MB INT , Database_Name VARCHAR(256) , CollectedOn DATETIME DEFAULT GETDATE() ) …and then filling it with: INSERT INTO [dbo].[CachedPages] ( [cached_pages_count] , [MB] , [Database_Name] ) SELECT COUNT(*) AS cached_pages_count , ( COUNT(*) * 8.0 ) / 1024 AS MB , CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE DB_NAME(database_id) END AS Database_name FROM sys.dm_os_buffer_descriptors GROUP BY database_id After this has been left logging your system metrics for a while you can easily see how your databases use the cache over time and may see some spikes that warrant your attention. This sort of logging can be applied to all sorts of server statistics so that you can gather information that will give you baseline data on how your servers are performing. This means that when you get a problem you can see what statistics are out of their normal range and target you efforts to resolve the issue more rapidly.

    Read the article

  • Parsing SQLIO Output to Excel Charts using Regex in PowerShell

    - by Jonathan Kehayias
    Today Joe Webb ( Blog | Twitter ) blogged about The Power of Regex in Powershell, and in his post he shows how to parse the SQL Server Error Log for events of interest.  At the end of his blog post Joe asked about other places where Regular Expressions have been useful in PowerShell so I thought I’d blog my script for parsing SQLIO output using Regex in PowerShell, to populate an Excel worksheet and build charts based on the results automatically. If you’ve never used SQLIO, Brent Ozar ( Blog...(read more)

    Read the article

  • Parsing SQLIO Output to Excel Charts using Regex in PowerShell

    - by Jonathan Kehayias
    Today Joe Webb ( Blog | Twitter ) blogged about The Power of Regex in Powershell, and in his post he shows how to parse the SQL Server Error Log for events of interest. At the end of his blog post Joe asked about other places where Regular Expressions have been useful in PowerShell so I thought I’d blog my script for parsing SQLIO output using Regex in PowerShell, to populate an Excel worksheet and build charts based on the results automatically. If you’ve never used SQLIO, Brent Ozar ( Blog | Twitter...(read more)

    Read the article

  • Why the “Toilet” Analogy for SQL might be bad

    - by Jonathan Kehayias
    Robert Davis(blog/twitter) recently blogged The Toilet Analogy … or Why I Never Recommend Increasing Worker Threads , in which he uses an analogy for why increasing the value for the ‘max worker threads’ sp_configure option can be bad inside of SQL Server.  While I can’t make an argument against Robert’s assertion that increasing worker threads may not improve performance, I can make an argument against his suggestion that, simply increasing the number of logical processors, for example from...(read more)

    Read the article

  • Bob Dorr’s SQL I/O Presentation on PSS Blog

    - by Jonathan Kehayias
    In case you missed it, Bob Dorr from the PSS Team posted an amazing blog post today yesterday with all of the slides and speaker notes from his SQL Server I/O presentation.  This is a must read for and Database Professional using SQL Server. http://blogs.msdn.com/psssql/archive/2010/03/24/how-it-works-bob-dorr-s-sql-server-i-o-presentation.aspx Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!...(read more)

    Read the article

  • Bob Dorr’s SQL I/O Presentation on PSS Blog

    - by Jonathan Kehayias
    In case you missed it, Bob Dorr from the PSS Team posted an amazing blog post today yesterday with all of the slides and speaker notes from his SQL Server I/O presentation.  This is a must read for and Database Professional using SQL Server. http://blogs.msdn.com/psssql/archive/2010/03/24/how-it-works-bob-dorr-s-sql-server-i-o-presentation.aspx Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!...(read more)

    Read the article

  • An XEvent a Day (11 of 31) – Targets Week – Using Multiple Targets to Debug Orphaned Transactions

    - by Jonathan Kehayias
    Yesterday’s blog post Targets Week – etw_classic_sync_target covered the ETW integration that is built into Extended Events and how the etw_classic_sync_target can be used in conjunction with other ETW traces to provide troubleshooting at a level previously not possible with SQL Server. In today’s post we’ll look at how to use multiple targets to simplify analysis of Event collection. Why Multiple Targets? You might ask why you would want to use multiple Targets in an Event Session with Extended...(read more)

    Read the article

  • Why I don’t need to go on the SQLCruise

    - by Jonathan Kehayias
    Brent Ozar ( Blog | Twitter ) and Tim Mitchell ( Blog | Twitter ) are putting on a new type of event in the month of August after SQL Saturday #40 in South Florida July, 31st , properly named SQLCruise .  The concept is great, at least in my opinion, you pay for a cruise, get to have a break, and at the same time attend a mini-conference on SQL Server with training provided by two great speakers.  The cost is relatively affordable, so what could possibly make it better?  How about...(read more)

    Read the article

  • Why I don’t need to go on the SQLCruise

    - by Jonathan Kehayias
    Brent Ozar ( Blog | Twitter ) and Tim Ford ( Blog | Twitter ) are putting on a new type of event in the month of August after SQL Saturday #40 in South Florida July, 31st , properly named SQLCruise . The concept is great, at least in my opinion, you pay for a cruise, get to have a break, and at the same time attend a mini-conference on SQL Server with training provided by two great speakers. The cost is relatively affordable, so what could possibly make it better? How about a sponsor offering up...(read more)

    Read the article

  • An XEvent a Day (10 of 31) – Targets Week – etw_classic_sync_target

    - by Jonathan Kehayias
    Yesterday’s post, Targets Week – pair_matching , looked at the pair_matching Target in Extended Events and how it could be used to find unmatched Events.  Today’s post will cover the etw_classic_sync_target Target, which can be used to track Events starting in SQL Server, out to the Windows Server OS Kernel, and then back to the Event completion in SQL Server. What is the etw_classic_sync_target Target? The etw_classic_sync_target Target is the target that hooks Extended Events in SQL Server...(read more)

    Read the article

  • Some thoughts on interviewing….

    - by Jonathan Kehayias
    At the beginning of the year I changed jobs, leaving a very stable position where I had the opportunity to learn under an amazing mentor (who happened to be a Oracle DBA and not a SQL DBA), to take on a job that I felt was much more challenging and had better potential for personal as well as professional growth.  I wasn’t necessarily looking for another job at the time, but one that interested me was mentioned at our local user group meeting and I decided to check it out and see if it was something...(read more)

    Read the article

1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >